2

I'm a bit stuck here trying to copy lots of data (a million records total) from three related tables to other three related tables in the same database.

My table design is as follows:

enter image description here

What I need is to be able to copy data from the draft tables over to the non-draft tables in one transaction which I'm able to roll back if anything goes wrong. This is needed because we don't want i.e. Billing and BillingPriceLine records to exist in the database, if the bulk insertion of BillingPriceLineSpecificationDraft copy failed.

However, since I'm using SqlBulkCopy for copying the records, I am not able to get a hold of the new IDs to make the correct relations between the three new tables. If I perform a read on i.e. the Billing table in the transaction to get the correct Billing ID, I get a time out, which is expected since the tables are locked within the transaction.

I have tried setting the IsolationLevel enum on the transaction (in fact, I went crazy and tried them all ;-)), but they didn't do anything it seems.

Is there any good way of doing this that I'm missing?

Thanks in advance.

bomortensen
  • 3,346
  • 10
  • 53
  • 74

1 Answers1

4

You can not really do that with bulk copy.

You also do not need to - it is a very bad practice to touch real tables with SqlBulkCopy because whoever wrote that thing, has no understanding of SQL Server locking mechanisms.

  • Create 3 temporary tables with the same table structure as the target.
  • SqlBulkCopy into those tables (thus avoiding SqlBulkCopy locking screwing you and avoiding the transaction issue).
  • Then, in one transaction, issue 3 INSERT INTO statements using the data from the temporary tables as source.

Result is the best of both worlds. Once you have the data in the temporary tables (with possibly fake ID's) you can pull some really interesting SQL to upate the source table in one transaction with the relevant internal ID's for the next table. The SQL gets complex, but this is the best you can do.

Explanation why SqlBulkCopy is bad:

SqlBulkCopy really is highly problematic - it does screw locking. It demands an exclusive table lock (which is "sort of ok"). It asks for it - but it never waits (i.e. if there is any lock on the table, this fails), then it tries again.... after some time.... until a timeout expires. The result is that on a table with activity it is really hard to get SqlBulkCopy to get ANY lock. The proper code would be to wait for the exclusive lock... well.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • Hi TomTom, thank you very much for your reply, it does make sense :-) I'm able to succesfully bulk insert into the three temporary tables, but I'm a bit in a loss when it comes to the insert statements since the `BillingPriceLine` should be related to the `Billing` record and vica verca. Do you, by any chance, have an example somewhere that shows how to make these inserts? :-) Thanks again! – bomortensen May 17 '16 at 07:35
  • 1
    No, not as example. I do tons of inserts, but i have natural keys on most cases. Remember: you can add another field to the temp table with fake id's, which you generate client side. Then use them for the joins. This is not that complicated - but it is "real" sql, not the trivia stuff most people do these days. – TomTom May 17 '16 at 08:50
  • Hi TomTom, I get what you're saying :-) I made it work by using the `OUTPUT` MSSQL clause to store both draft ID and inserted ID and created the relations that way. Works perfectly. Thanks for your help! – bomortensen May 17 '16 at 12:25
  • Gratulations - you got it. That is the way to go ;) – TomTom May 17 '16 at 12:26
  • @TomTom I have a similar problem. The difference is I am trying to copy data from tab-delimited files to a table in the database that are linked by the foreign keys. So this operation needs to be sequential. Also, I do not have the option of creating temporary tables and following the process described here. Is there any way to achieve this? – Zankhana Rana Jan 14 '20 at 22:13
  • Currently, this is what I am doing - In a transaction, read the file, delete existing values in the table and then, bulkcopy into the table. If there is any violation, the entire operation is rolledback. My problem is, the tables are not actually updated until the transaction is committed and the operation fails because of the foreign key violation as for the latter table it is making the relation still with the old table and not with the one that is coming from the file. – Zankhana Rana Jan 14 '20 at 22:14