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

we converting database primary keys guids auto-incremented ints. have data parse text files , put 2 c# datatables claim , claimcharge have been using bulk insert identically named tables in database. in database, claimcharge.claimid foreign key claim.id , several claim charges exist 1 claim.

with guids generated claim , claimcharge ids in c#, bulk inserting no problem. ints, don't know claim.id be, can't assign claimcharge.claimid. need ideas on how accomplished ints.

for instance, if claim table manually locked against inserts, could:

1. bulk insert alternate tables named claimbulkdata claimchargebulkdata. these tables still use guids convenience in keeping relationship maintained between c# , sql.
2. manually lock claim table against inserts (don't know if possible) , max(id).
3. increment of data in claimbulkdata using max(id).
4. associate claimchargebulkdata claimbulkdata using newly updated int
5. insert data real claim table set using identity_insert on using kind of exception imaginary lock created in step 2.
6. release manually created lock against inserts on claim table (again don't know if possible.
7. insert data real claimcharge table.

i want avoid inserting data 1 row @ time in either c# or t-sql.

why not add new auto-increment column master tables -- have both guid , autoid column can fix foreign key relationship (one master table @ time)

i.e.,

assume have master1 , detail1 , 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 , detail12 based on joining master1 on oldguid key set corresponding value of master1id each table

you can add foreign keys based on master1id detail , detail2

at point should have complete set of data based on both sets of keys, , can test update views, etc. make sure work new integer ids

finally, once cool, drop unneeded guid foreign key , guid columns themselves.

you can run database pack once clean , converted if intent reduce overall disk usage via restructuring. point of work fixups foreign keys in process this.