0

I have 2 questions about SqlBulkCopy:

  1. If I insert a batch of 1000 records and the 990th item doesn't meet validation criteria, does the other 999 records still get inserted into the database or not?

  2. How do you track errors caused by SqlBulkCopy. I have some code here but was wondering how would I track validation errors:

user7393401
  • 21
  • 7

1 Answers1

1

SQL Bulk Copy transaction behaviour is covered in the MSDN documentation.

If you want a more fine degree of control, the general method is to BCP into a temporary table, then validation check and insert/update from there into live.

Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
PhillipH
  • 6,182
  • 1
  • 15
  • 25
  • How would I insert into a temp table, check validation and then import to the live table? – user7393401 Jan 12 '17 at 10:24
  • @Hofman can you explain your statement of temp tables with a code sample as it is confusing me? – user7393401 Jan 12 '17 at 11:15
  • @user7393401 - step 1 : insert your data into a table that is a structural copy of your destination using SqlBulkCopy, but has no data in it. Step 2 : Use standard SQL like SELECT to find data in your temporary table that does not fit your business rules, and delete that data. Step 3 : Insert the remaining good data from the temporary table to the live table. Step 4 : Don't chase people who are trying to help you by posting "Anyone ???" - we all have a job to do other than helping strangers. According to SO you posted 3 comments within 3 hours to my post, the final one "Anyone ???". – PhillipH Jan 12 '17 at 20:41
  • Thanks for your response @PhillipH. I understand what you are saying. Consider this, I create a a structural copy of the destination table which contains (id, fkId, name...) but the file content that I want to upload to this table has the format (fkId, name...) i.e. not the id itself. I want to select all the data from the file, but when it comes to inserting data into the table, how do I deal with the auto generated id column? – user7393401 Jan 13 '17 at 09:25
  • Forgot to mention, this needs to be very efficient. – user7393401 Jan 13 '17 at 11:33
  • Could I not just put my validation as: MERGE Customer AS [Target] USING StagingCustomer [put select filters here] AS [Source] – user7393401 Jan 13 '17 at 16:24
  • In terms of the missing ID column - whats the issue ? Just dont insert it and SQL will auto generate it for you. As for efficiency; Bulk Copy plus Merge is the most efficient way to load data into an MSSQL database. If you have a further different question, please post (with example code) as a seperate question. – PhillipH Jan 13 '17 at 17:32