CODEWITHSUNDEEP
sqlsql-servercsvinsert
aasenomad
aasenomad

Reputation: 469

How to insert bulk csv file into SQL Server

I have a csv file that have columns like this

"Hold","EmpID","Source","Shard-Exists","Type"

but my DB table look like this

//Note: My Id is auto increment
"Id","Hold","EmpID","Source","Type","CurrentDate"

I'm just wondering how can bulk insert my csv file into the database table without the shard-Exist column and also passing the Current Date automatically.

Any help or suggestion will be really appreciated

TRUNCATE TABLE dbo.Actors;
GO
 
-- import the file
BULK INSERT dbo.Actors
FROM 'C:\Documents\Skyvia\csv-to-mssql\actor.csv'
WITH
(
        FORMAT='CSV',
        FIRSTROW=2
)
GO

Upvotes: 1

Views: 982

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

You should be able to use a format file to accomplish the 'skip columns in table' task. I'll modify the example from the MS docs.

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="2" NAME="Hold" xsi:type="SQLINT"/>
  <COLUMN SOURCE="3" NAME="EmpID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="4" NAME="Source" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="Type" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Note, in the <ROW> portion that I'm not specifying anything for the ID or CurrentDate columns. It's also noteworthy that there's no SOURCE="5"; that's how the Shard-Exists field in the source data is being skipped.

As to auto-generating a value for CurrentDate, my recommendation would be to add a default constraint to your table. that can be done like so:

ALTER TABLE dbo.Actors
   ADD CONSTRAINT DF_Actors__CurrentDate
   DEFAULT (getdate()) FOR CurrentDate;

Upvotes: 1

Related Questions