Bulk Insert CSV into a SQL Server table is common task for developers and DBAs.

Before starting find out :

1)the FIELDTERMINATOR –

2)row terminator

 CSV is defined as Comma Separated Values . Despite the “comma” word , it is possible to use other characters – as the FIELDTERMINATOR , just ensure it is unique and does not interfere with other text .

 Create the CSV file and save as “sqlserver-dba-csv.txt”

 1,James Brown,blue

2,Prince,red

3,Rick James,yellow

 --create a table

CREATE TABLE musicians_csv (

musician_id INT,

full_name VARCHAR(50),

colour VARCHAR(20)

)

GO

--bulk insert csv into a SQL Server table

BULK

INSERT musicians_csv

FROM 'h:\sqlserver-dba-csv.txt'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

GO

--Verify data inserted

SELECT *

FROM musicians_csv

GO

--Drop the table

DROP TABLE musicians_csv

GO

Bulk Insert CSV into a SQL Server table offers other arguments – beyond FIELDTERMINATOR and ROWTERMINATOR. Other arguments are:
BATCHSIZE ,CHECK_CONSTRAINTS,CODEPAGE,DATAFILETYPE ,FIELDTERMINATOR,FIRSTROW,FIRE_TRIGGERS,FORMATFILE, KEEPIDENTITY,KEEPNULLS,KILOBYTES_PER_BATCH,LASTROW ,MAXERRORS,ORDER,ROWS_PER_BATCH,ROWTERMINATOR, TABLOCK,ERRORFILE
Check BOL for indepth details

 

 If you see messages  , such as below while commiting an import Bulk Insert CSV into a SQL Server table  , use this tactic.

1)insert the data into a staging table – apply the necessary conversions , and then migrate to main table.

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 36 (Last Modified Date).

 

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Author: Jack Vamvas (http://www.sqlserver-dba.com)