TSQL: UPDATE with INSERT INTO SELECT FROM

Question

so I have an old database that I'm migrating to a new one. The new one has a slightly different but mostly-compatible schema. Additionally, I want to renumber all tables from zero.

Currently I have been using a tool I wrote that manually retrieves the old record, inserts it into the new database, and updates a v2 ID field in the old database to show its corresponding ID location in the new database.

for example, I'm selecting from MV5.Posts and inserting into MV6.Posts. Upon the insert, I retrieve the ID of the new row in MV6.Posts and update it in the old MV5.Posts.MV6ID field.

Is there a way to do this UPDATE via INSERT INTO SELECT FROM so I don't have to process every record manually? I'm using SQL Server 2005, dev edition.

Solution 1

The key with migration is to do several things: First, do not do anything without a current backup. Second, if the keys will be changing, you need to store both the old and new in the new structure at least temporarily (Permanently if the key field is exposed to the users because they may be searching by it to get old records).

Next you need to have a thorough understanding of the relationships to child tables. If you change the key field all related tables must change as well. This is where having both old and new key stored comes in handy. If you forget to change any of them, the data will no longer be correct and will be useless. So this is a critical step.

Pick out some test cases of particularly complex data making sure to include one or more test cases for each related table. Store the existing values in work tables.

To start the migration you insert into the new table using a select from the old table. Depending on the amount of records, you may want to loop through batches (not one record at a time) to improve performance. If the new key is an identity, you simply put the value of the old key in its field and let the database create the new keys.

Then do the same with the related tables. Then use the old key value in the table to update the foreign key fields with something like:

Update t2
set fkfield = newkey
from table2 t2
join table1 t1 on t1.oldkey = t2.fkfield

Test your migration by running the test cases and comparing the data with what you stored from before the migration. It is utterly critical to thoroughly test migration data or you can't be sure the data is consistent with the old structure. Migration is a very complex action; it pays to take your time and do it very methodically and thoroughly.

Solution 2

Probably the simplest way would be to add a column on MV6.Posts for oldId, then insert all the records from the old table into the new table. Last, update the old table matching on oldId in the new table with something like:

UPDATE mv5.posts
SET newid = n.id
FROM mv5.posts o, mv6.posts n 
WHERE o.id = n.oldid

You could clean up and drop the oldId column afterwards if you wanted to.

Solution 3

The best you can do that I know is with the output clause. Assuming you have SQL 2005 or 2008.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

It still would require a second pass to update the original table; however, it might help make your logic simpler. Do you need to update the source table? You could just store the new id's in a third cross reference table.

Solution 4

Heh. I remember doing this in a migration.

Putting the old_id in the new table makes both the update easier -- you can just do an insert into newtable select ... from oldtable, -- and the subsequent "stitching" of records easier. In the "stitch" you'll either update child tables' foreign keys in the insert, by doing a subselect on the new parent (insert into newchild select ... (select id from new_parent where old_id = oldchild.fk) as fk, ... from oldchild) or you'll insert children and do a separate update to fix the foreign keys.

Doing it in one insert is faster; doing it in a separate step meas that your inserts aren't order dependent, and can be re-done if necessary.

After the migration, you can either drop the old_id columns, or, if you have a case where the legacy system exposed the ids and so users used the keys as data, you can keep them to allow use lookup based on the old_id.

Indeed, if you have the foreign keys correctly defined, you can use systables/information-schema to generate your insert statements.

Solution 5

Is there a way to do this UPDATE via INSERT INTO SELECT FROM so I don't have to process every record manually?

Since you wouldn't want to do it manually, but automatically, create a trigger on MV6.Posts so that UPDATE occurs on MV5.Posts automatically when you insert into MV6.Posts.

And your trigger might look something like,

create trigger trg_MV6Posts
on MV6.Posts
after insert
as
begin
    set identity_insert MV5.Posts on

    update  MV5.Posts
    set ID = I.ID
    from    inserted I

    set identity_insert MV5.Posts off
end

View more solutions

Comments

[ninesided]

I actually think that leaving the oldId on the new table is preferable to having a a newId on the old table, which makes this even easier.