Copying large amount of data in multiple related tables

[SHelton]

Can anyone suggest a decent strategy for copying (potentially) large amounts of data into the same tables in a database ?

To explain, I have a database which holds estimate details in a number of related tables. The client would like a facility to copy an estimate, creating a clean version which can be modified leaving the source estimate untouched.

Obviously the copied data would need to maintain the relations between parent and child rows, so I assume bulk copy is not viable.

The application is .NET, and it has been suggested that the only way is to load a DataSet with the source estimate, then insert the rows back into the database, retrieving the keys as each row is inserted. The child rows would then have the foreign key value updated before they were inserted.

This seems an inefficient way to copy data, so any suggestions would be greatly appreciated.

 

[gmmastros]

Insert Into... Select

Ex:

Insert Into Table(Id, Col1, Col2, etc...)
Select 400, Col1, Col2, etc...
From   Table
Where  Id = 100

-George

"the screen with the little boxes in the window." - Moron

 

[SHelton]

Thanks for the quick reply gmmatros. But.....

If a row in TableA has 15 related rows in TableB, when I use Insert Into...Select for TableA how will I know what the primary key (which is an identity column) is to be able to insert the TableB rows with the correct foreign key ?

 

[gmmastros]

That is precisely what Scope_Identity() is for.

When inserting in to table A, an identity value will be inserted. Then, you use Scope_Identity() to get the value that was inserted, and finally, use it in the insert for table B.

Declare @Id Int

Insert Into TableA(Col1, Col2)
Select Col1, Col2
From   TableA
Where  Id = 20

Select @Id = Scope_Identity()

Insert Into TableB(TableAId, Col1, Col2)
Select @Id, Col1, Col2
From   TableB 
Where  TableAId = 20

Does this make sense?

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

And if you're doing inserts en masse, you can still use scope_identity():

Put the rows to be inserted in a temp table with an identity column.
Insert the rows to the new table with a column that is the temporary identity value from your temp table, retrieving @@rowcount and scope_identity().
Using this information you can join back to the temp table and do whatever work you need, knowing the newly assigned identity values.
When finished, clean out or drop the temp work column from your inserted table.

If this is a process that's going to happen over and over again for the foreseeable future, you can also put a trigger on table A that inserts to table B (and has all the information it needs to know what column ended up where via the Inserted metatable).

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[SHelton]

Thanks for the help guys.

I'm going to look at a stored procedure which uses temp tables. I can call it asynchronously from the app which will allow the user to carry on doing other stuff while the copy is carried out.