19 November,2012 by Jack Vamvas
Bulk Insert CSV into a SQL Server table is a common task for SQL developers and DBAs. Normally a large file is used as a source of delimited data. A useful feature of BULK INSERT is the ERRORFILE argument.
The ERRORFILE records rows with formatting issues. The BULK INSERT process copies the rows into the error file. As well as the log file created to report the rows , another file is created , with the .ERROR.txt suffix, whose purpose to report on the error reasons .
The BULK INSERT process below created the following errors:
When using Data Example 2 - Two files are created
bulk_insert_BadData.log - with the problem rows
bulk_insert_BadData.log.Error.Txt - Row 1 File Offset 0 ErrorFile Offset 0 - HRESULT 0x80020005
Create the CSV file and save as “sqlserver-dba-csv.txt”
Data Example 1
1, James Brown, blue
Data Example 2 (Note:no commas in the first row, generates error)
1 James Brown blue
--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 'k:\bulk_insert.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', ERRORFILE = 'K:\bulk_insert_BadData.log' ) GO --Verify data inserted SELECT * FROM musicians_csv GO --Drop the table DROP TABLE musicians_csv GO