2

I get a CSV file with a several million records every few days. I need to insert them into our database. What I do is quite straightforward:

BULK INSERT dbo.activity
FROM 'C:\tmp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

This works well. However, it may be the case that I get some duplicated records. When I run the import I, as expected, get the error:

Violation of PRIMARY KEY constraint 'PK__id__790FF7A****'. Cannot insert duplicate key in object 'dbo.activity'. The duplicate key value is (1234)

This gets quite cumbersome when instead of one duplicated record I have hundreds. Is there any way to tell BULK INSERT to ignore (or log) those errors and carry on with the good records? I am thinking about something like:

BEGIN TRY:
  BULK INSERT dbo.activity
  FROM 'C:\tmp\activity.csv'
  WITH (options....)
END TRY
BEGIN CATCH:
  SELECT
        ERROR_NUMBER() AS ErrorNumber  
       ,ERROR_MESSAGE() AS ErrorMessage;
   -- I know this line wrong.  It is just what I would like to do
  CONTINUE;
END CATCH

Thanks!

3 Answers 3

2

I think a good approch would be to have a staging table (without PK constraint).

You will load your data from your bulk command into this table and then, you should be able to script down the logic you want to implement into a store procedure that you can run after

Ex: Check for duplicate, log them to another table or send a mail, delete the duplicate base on some logic and then insert the remaining rows into the target table.

1
  • Hi @DominiqueBoucher. Thanks for you comment. Yes, that is an option but its some more space an processing requirements. But, in the last case that would be an option.
    – Wilmar
    Oct 21, 2020 at 21:27
0

After reading again BULK INSERT documentation I think this may work. It is not elegant but for my scenario it could do it. It require me to know the number of records in the file. That is easy enough to get with:

wc -l 'C:\tmp\activity.csv'

Then with that number I would do:

-- Let's suppose I have one million records (Usually I have several million)
DECLARE @max_records bigint = 1000000

BULK INSERT dbo.activity
FROM 'C:\tmp\activity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = max_records,
    ERRORFILE = 'C:\tmp\bulik_insert.log'
)
GO

Then it would simply a matter of double checking the log file to make sure the errors are related to the key constraint and not something else.

What I don't know is how taxing this would be for the system.

2
  • 1
    The errors logged will be bulk insert parsing errors, not PK violations. The entire batch will be all-or-none.
    – Dan Guzman
    Oct 21, 2020 at 22:33
  • You are right @DanGuzman. Then the error file is pretty useless to me.
    – Wilmar
    Oct 22, 2020 at 12:19
0

If you don´t want to use a staging table (which would be my preference) you could look at defining the primary key with ignore_dup_key set. An excellent article discussing pros and cons with this approach below.

https://sqlperformance.com/2019/04/sql-performance/ignore_dup_key-slower-clustered-indexes

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.