Large Export Of Data From One DB Of One Structure To Another

Hi guys,

Hopefully this is the right place to ask.

Basically we have have two larges databases, one of which is updated from the other monthly.

For exaplination purposes:

DB1 = Source DB

DB2 = Destination DB

The problem that I require a soltion to is, how do I insert rows from a table in DB1 to DB2 and recover and store the identity of the new row against the ID of the existing row. This is so that I can then matain constraints when it comes to inserting rows into the next table and the next and so on.

This process of storing the ID's as lookups will need to be done for almost every table of which there are 20.

The best Idea we have at the minute is to create a table with two colums for each table (drop it and recreate it after each table has exported) that contains the two ID's, new and old.

This will require using a cursor for each row in the existing table, inserting it in the new table and the using @@Scope_Identity to get the new ID and then insert the two values into the temp table.

This too me feels like it will be very slow, particuarly when I bear in mind how much data we have.

Does anyone have any better ideas? (Sorry if the explaination isn't great, its difficult to get accross)

Thanks

Ed