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).

Republished from http://www.sqlserver-dba.com.