Bulk Insert CSV into a SQL Server table is common task for developers and DBAs.
Before starting find out:
1)the FIELDTERMINATOR –
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”
--create a table
CREATE TABLE musicians_csv (
--bulk insert csv into a SQL Server table
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
--Verify data inserted
--Drop the table
DROP TABLE musicians_csv
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.
Republished from SQL Server DBA [65 clicks].
Read the original version here [32134 clicks].