Stuck trying to move two tables from one database to another database

Question

I am trying to transfer some data from two tables to an OLD database in a new database.

The problem is that I want to create a new primary key in a new database for the first table to be imported. It's simple.

But the second table in the old database has a foreign key dependency on the first table. Therefore, when I want to transfer old data from the second table, the foreign key no longer matches.

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

Serious note: I cannot change the current schema of new tables that does not have the "old identifier" column.

The following table schema is used: -

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 layout is identical.

Thoughts?

EDIT:

For those who ask the question, RDBMS is Sql Server 2008. I did not specify the software because I was hoping to get an agnostic response with some common 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 identifiers (and generate new ones). Then, when they are in the new database, and they have both new and old identifiers, you can use the old identifier to link the new identifiers, and then discard the old identifiers.

You can do this by importing into temporary (that is, they will be thrown away) tables, and then insert into the permanent tables, leaving the old identifiers.

Or import directy into new tables (with a modified schema to also keep the old identifiers), then discard the old identifier when they are no longer needed.

EDIT:

Ok, I understand a little what you are looking for thanks to the comments here and the other answers. I knocked it down, I think he will do what you want.

Basically without cursors, it goes through the parent table, builds after the row, and inserts a new parent row and all the child rows for that parent row, keeping the new identifier in sync. I tried this and it should work, it does not need exclusive access to the tables and should be the order of magnification faster than the 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 think you will need to define other criteria for creating a map like oldPK => newPK (for example: Name field is equal?

You can then identify the new PC that matches the old PC and adjust the ParentID .

You can also do a little trick: add a new column to the original table1, which records the new PK value for the copied record. Then you can easily copy the values ​​of Table2 by pointing them to the value of the new column instead of the old PK.

EDIT . I am trying to provide an example code of what I meant by my little trick. I do not change the original database structure, but now I am using a temporary table.

OK, you can try the following:

1) Create a temporary table containing the values ​​of the old table, plus it will get a new PK:

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

2) Paste all the values ​​from the old table into the temporary table, calculating the new PK, copying the old PK:

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

3) Copy the values ​​to a new table

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

4) Copy the values ​​from table 2 to NewTable2

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

It has to be done. Please note that this is only T-SQL pseudo-code - I have not tested this in a real database! However, it should come close to what you need.

Answer 3

Can you change the layout of old tables? If so, you can put the “new identifier” column in the old tables and use this as a link.

You may need to do row-by-row insertion in the new table, and then get the scope_identity identifier, save it in the old table1. But for table 2, you can join the old table1 and capture new_id.

Answer 4

Firstly, can you even have some kind of temporary scheme that can later be reset ?! It would make life easier. Assuming you cannot:

If you are lucky (and if you can guarantee that other inserts will not happen at the same time), then when you insert Table1 data into a new table, you can cheat based on the sequential order of insertions.

You can then create a view that joins 2 tables in a row count so that you have a way to map keys to each other. This way you’ll be one step closer to being able to identify “ParentId” for the new table2.

Answer 5

I'm not sure about your question which database software you are 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 that you created earlier, then use "insert into select from" (or any other appropriate command for your database) to copy revised temporary table to its permanent place.

Answer 6

I had a great opportunity to look into migration scenarios last summer. I used Oracle PL / SQL for this task. But you did not indicate what technology you are using? What do you transfer data to? SQL Server? Oracle? MySQL?

The approach is to insert a row from table1. IMPROVE the new primary key generated (possibly through SEQUENCE [in Oracle]), and then INSERT dependent records from table2 by changing the value of their foreign key to the value returned by the first INSERT, I can’t help you better if you cannot specify which DBMS you transfer data.

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