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

Question

Hi folks,

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

Can you change the schema of the old tables? If so, you could put a "new id" column on the old tables, and use that as the reference.

You might have to do a row by row insert on the new table and then retrieve the scope_identity, store it in the old table1. But for table2, you can then join to the old table1 and grab the new_id.

Answer 2

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 3

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.

Answer 4

First of all - can you not even have some temporary schema that you can later drop?! That would make life easier. Assuming you can't:

If you're lucky (and if you can guarantee that no other inserts will be happening at the same time) then when you insert the Table1's data into your new table you could perhaps cheat by relying on the sequential order of the inserts.

You could then create a view that joins the 2 tables on a row-count so that you have a way to correlate the keys to each other. That way you'd be one step closer to being able to identify the 'ParentId' for the new Table2.

Answer 5

I'm not sure from your question what database software you're using, but if temporary tables are an option, create a temporary table containing the original primary key of table1 and the new primary key of table1. Then create another temporary table with a copy of table2, update the copy using the "old key, new key" table you created earlier, then use "insert into select from" (or whatever the appropriate command is for your database) to copy the revised temporary table into its permanent location.

Answer 6

I had the wonderful opportunity to be dug deep in migration scripts last summer. I was using Oracle's PL/SQL for the task. But you did not mention what technology are you using? What are you migrating the data into? SQL Server? Oracle? MySQL?

The approach is to INSERT a row from table1 RETURING the new primary key generated (probably by a SEQUENCE [in Oracle]) and then INSERT the dependent records from table2, changing their foreign key value to the value returned by the first INSERT. Can't help you any better unless you can specify what DBMS are you migrating data into.

Answer 7

The following Pseudo-ish code should work for you

CREATE TABLE newtable1
  ParentId INT PK
  OldId   INT
  Name     VARCHAR(50)

CREATE TABLE newtable2
  ChildId    INT pk
  ParentId   INT FK
  OldParent  INT
  Foo        VARCHAR(50)   

  INSERT INTO newtable1(OldId, Name)
  SELECT  ParentId, Name FROM oldtable1

  INSERT INTO newtable2(OldParent, Foo)
  SELECT ParentId, Foo FROM oldtable2

UPDATE newtable2 SET ParentId = (
  SELECT n.ParentId 
  FROM   newtable1 AS n
  WHERE  n.OldId = newtable2.oldParent
)

ALTER TABLE newtable1 DROP OldId
ALTER TABLE newtable2 DROP OldParent