Changing IDs and updating tables.

Question

We are trying to bring two different databases together but both databases are using the same IDs. Different info but we would have two users with same ID. Is there a way that we can change the IDs in one database and then update each of the tables using that ID?

Answer 1

If you have no dependencies to take care of, a really stupidly simple solution comes to mind:

Pseudocode:

SELECT max(id) FROM database1.tablename
// Returns 10389

UPDATE database2.tablename SET id = (id + 10389)
// Sets 1st record to 10390
//      2nd record to 10391
//      3rd record to 10392
......

Answer 2

If its an auto increment id column you can just do:

SELECT //(columns except id)
INTO new_table_name [IN otherdatabase]
FROM old_tablename

and tadaa!

Answer 3

We are working on a project like that and the answer is staging tables. Stage the data you are going to insert and add a column for the new id. Insert the parent records and update the new id in the staging table (SQL server is helpful her with the output clause, I don't know about other dbs). Now when you you insert the child records, you use the new id instead of the old one. An alternative is to add a column for the old id to the parent table. Use it to join to get the new id when inserting to child tables. When all the data is migrated remove the column.

Answer 4

assumes no other processing is going on in database 1 or database 2, give this a try:

1) In database 1 get max ID

2) in database 2, drop all foreign keys related to this ID

3) in database 2, on the table where ID is an auto increment/identity, you will have to add a new column ID2 and populate it with UPDATE YourTable SET ID2=ID+{max ID from database 1}, then drop the original ID column, then rename ID2 to ID

4) in database 2, for all other tables that use this ID as a FK, change the IDs using UPDATE OtherTable SET ID=ID+{max ID from database 1}

5) in database 2, restore foreign keys to validate data

6) in database 2, export data to a file

7) in database 1, import the file from database 2