Stuck trying to migrate two tables from one DB to another DB

Question

i'm trying to migrate some data from two tables in an OLD database, to a NEW database.

The problem is that I wish to generate new Primary Key's in the new database, for the first table that is getting imported. That's simple.

But the 2nd table in the old database has a foreign key dependency on the first table. So when I want to migrate the old data from the second table, the foreign key's don't match any more.

Are there any tricks/best practices involved to help me migrate the data?

Serious Note: i cannot change the current schema of the new tables, which do not have any 'old id' column.

Lets use the following table schema :-

Old Table1              New Table1
ParentId INT PK         ParentId INT PK
Name VARCHAR(50)        Name VARCHAR(50)

Old Table 2             New Table 2
ChildId INT PK          ChildId INT PK
ParentId INT FK         ParentId INT FK     
Foo VARCHAR(50)         Foo VARCHAR(50)

So the table schema's are identical.

Thoughts?

EDIT:

For those that are asking, RDBMS is Sql Server 2008. I didn't specify the software because i was hoping i would get an agnostic answer with some generic T-Sql :P

Answer 1

I think you need to do this in 2 steps.

You need to import the old tables and keep the old ids (and generate new ones). Then once they're in the new database and they have both new and old ids you can use the old Id's to get associate the new ids, then you drop the old ids.

You can do this by importing into temporary (i.e. they will be thrown away) tables, then inserting into the permanent tables, leaving out the old ids.

Or import directy into the new tables (with schema modified to also hold old ids), then drop the old id's when they're no longer necessary.

EDIT:

OK, I'm a bit clearer on what you're looking for thanks to comments here and on other answers. I knocked this up, I think it'll do what you want.

Basically without cursors it steps through the parent table, row by row, and inserts the new partent row, and all the child rows for that parent row, keeping the new id's in sync. I tried it out and it should work, it doesn't need exclusive access to the tables and should be orders of magniture faster than a cursor.

declare @oldId as int
declare @newId as int

select @oldId = Min(ParentId) from OldTable1 

while not @oldId is null
begin
    Insert Into NewTable1 (Name)
    Select Name from OldTable1 where ParentId = @oldId

    Select @newId = SCOPE_IDENTITY()

    Insert Into NewTable2 (ParentId, Foo)
    Select @newId, Foo From OldTable2 Where ParentId = @oldId

    select @oldId = Min(ParentId) from OldTable1 where ParentId > @oldId

end

Hope this helps,

Answer 2

Well, I guess you'll have to determine other criteria to create a map like oldPK => newPK (for example: Name field is equal?

Then you can determine the new PK that matches the old PK and adjust the ParentID accordingly.

You may also do a little trick: Add a new column to the original Table1 which stores the new PK value for a copied record. Then you can easily copy the values of Table2 pointing them to the value of the new column instead of the old PK.

EDIT: I'm trying to provide some sample code of what I meant by my little trick. I'm not altering the original database structure, but I'm using a temporary table now.

OK, you might try to following:

1) Create temporary table that holds the values of the old table, plus, it gets a new PK:

CREATE TABLE #tempTable1
(
    newPKField INT,
    oldPKField INT,
    Name VARCHAR(50)
)

2) Insert all the values from your old table into the temporary table calculating a new PK, copying the old PK:

INSERT INTO #tempTable1
SELECT
   newPKValueHere AS newPKField,
   ParentID as oldPKField,
   Name
FROM
   Table1

3) Copy the values to the new table

INSERT INTO NewTable1
SELECT
   newPKField as ParentId,
   Name
FROM
   #tempTable1

4) Copy the values from Table2 to NewTable2

INSERT INTO NewTable2
SELECT
   ChildID,
   t.newPKField AS ParentId,
   Foo
FROM 
   Table2
   INNER JOIN #tempTable1 t ON t.ParentId = parentId

This should do. Please note that this is only pseudo T-SQL Code - I have not tested this on a real database! However, it should come close to what you need.