Bulk insert related sets of data with unknown auto-incremented IDs

Question

We are converting database primary keys from GUIDs to auto-incremented INTs. We have data that we parse from text files and put into two C# DataTables Claim and ClaimCharge that we have been using to bulk insert into identically named tables in the database. In the database, ClaimCharge.ClaimID is a foreign key to Claim.ID and several claim charges exist for one claim.

With GUIDs we generated the Claim and ClaimCharge IDs in C#, so bulk inserting was no problem. But with INTs, I don't know what the Claim.ID will be, so I can't assign ClaimCharge.ClaimID. I need some ideas on how this could be accomplished with INTs.

For instance, if the Claim table could be manually locked against inserts, I could:

1. Bulk insert into alternate tables named ClaimBulkData ClaimChargeBulkData. These tables would still use GUIDs for convenience in keeping the relationship maintained between C# and SQL.
2. Manually lock the Claim table against inserts (don't know if this is possible) and get the max(ID).
3. Increment all of the data in ClaimBulkData using MAX(ID).
4. Associate ClaimChargeBulkData to ClaimBulkData using the newly updated INT
5. Insert data into real Claim table as a set using IDENTITY_INSERT ON using some kind of exception to the imaginary lock created in step 2.
6. Release manually created lock against inserts on Claim table (again I don't know if this is possible.
7. Insert data into real ClaimCharge table.

I want to avoid inserting the data one row at a time in either C# or T-SQL.

Answer 1

Why not just add the new auto-increment column to the master tables -- you will then have both GUID and autoid column so you can fix up the foreign key relationship (one master table at a time)

i.e.,

Assume you have master1 and detail1 and detail1

alter table Master1 add ID int identity(1,1) not null
GO

alter Detail1 add master1ID int null
GO
alter Detail2 add master1ID int null
GO

Then update Detail1 and Detail12 based on joining Master1 on the oldguid key to set the corresponding value of Master1ID for each table

You can then add the foreign keys based on Master1ID to Detail and Detail2

At this point you should have a complete set of data based on both sets of keys, and you can test update views, etc. to make sure they work with the new integer ids

Finally, once all is cool, drop to unneeded GUID foreign key and the Guid columns themselves.

You can always run a database pack once you get everything clean and converted if your intent was to reduce overall disk usage via this restructuring. The point is much of the work is fixups for foreign keys in a process like this.