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.
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:
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.
Answer author Sqlzim
Tickanswer.com is providing the only single recommended solution of the question SQL Server Bulk Insert Skip Primary Key Violations under the categories i.e sql , sql-server , tsql , bulkinsert , . Our team of experts filter the best solution for you.