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

2,Prince,red

3,Rick James,yellow

Data Example 2 (Note:no commas in the first row, generates error)

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 '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

 Read More

Data Loading Performance Guide

SQL Server architecting write-intensive databases