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

--db1
CREATE TABLE MOVETO
(
iID uniqueidentifier,
iName nchar(50)
)

CREATE TABLE MOVETO_TABLETWO
(
iRowid int identity(1,1),
cAddress nvarchar(100)
)
--db2
CREATE TABLE MOVEFROM
(
TableTwo int Identity(1,1),
iID uniqueidentifier,
cAddress nvarchar(100)
)

CREATE PROCEDURE MOVEDATA
AS
BEGIN

CREATE #GETIDENTITY TABLE
(
iIdentity int identity(1,1)
oldidentity uniqueidentifier,
newidentity uniqueidentifier DEFAULT NEWID()
)
--get old and new identitys
INSERT INTO #GETIDENTITY(oldidentity)
SELECT iID
FROM MOVEFROM
WHERE ?

--now hookup new identitys to insert into MOVETO

INSERT INTO db2.MOVETO(iID,iName)
SELECT G.newidentity
FROM #GETIDENTITY G INNER JOIN MOVEFROM MF
ON G.oldidentity = MF.iID

--INSERT INTO MOVER_TABLETWO
INSERT INTO db2.MOVETO_TABLETWO(cAddress)
SELECT MF.cAddress
FROM #GETIDENTITY G INNER JOIN MOVEFROM MF
ON F.oldidentity = MF.iID

DROP TABLE #GETIDENTITY

END