Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - BULK INSERT with ERRORFILE

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

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



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

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer