CODEWITHSUNDEEP
sqlsql-servert-sqlbulkinsert
Night Train
Night Train

Reputation: 781

SQL Server Bulk Insert Skip Primary Key Violations

I'm currently using SQL Server's Native Bulk Insert procedure to load several tables into a database from text files.

This has been working well and even handles truncation,null,and formatting errors gracefully. If any column is too large, is null when it shouldn't be, or if there is an incorrect number of columns found based on the format file, those rows are skipped and forwarded to an error file.

Recently however, a duplicate primary key was entered into one of my text files. This caused the bulk insert to simply fail, no row was forwarded to the error file.

Currently my bulk insert commands look like this:

BULK INSERT table.table FROM 'data_file.txt' WITH(
    BATCHSIZE = 100000,
    CODEPAGE = 1252,
    DATAFILETYPE = 'char',
    FIRSTROW = 2,
    KEEPNULLS,
    MAXERRORS = 1000000000,
    TABLOCK,
    FORMATFILE = 'format_file.txt',
    ERRORFILE = 'error_file.txt'
)

Is there a way, using bulk insert, to handle primary key violations the same way other errors are handled? Ideally I would like rows containing PK violations forwarded to the same error file.

Upvotes: 5

Views: 7617

Answers (1)

SqlZim
SqlZim

Reputation: 38043

To directly answer your question, no.

UNIQUE, and PRIMARY KEY constraints are always enforced. When importing into a character column that is defined with a NOT NULL constraint, BULK INSERT inserts a blank string when there is no value in the text file.

I would recommend using a staging or temp table for your bulk insert and move it from there to the actual tables.

BULK INSERT without the CHECK_CONSTRAINTS option also has other ramifications for your table:

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS option, any CHECK and FOREIGN KEY constraints are ignored, and after the operation, the constraint on the table is marked as not-trusted.

Which can negatively impact performance of queries if you don't fix it after.

Upvotes: 6

Related Questions