C# TSQL：使用 INSERT INTO SELECT FROM 进行更新

TSQL: UPDATE with INSERT INTO SELECT FROM

提问by tsilb

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.

目前我一直在使用我写的一个工具，手动检索旧记录，将其插入到新数据库中，并更新旧数据库中的 v2 ID 字段以显示其在新数据库中对应的 ID 位置。

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.

例如，我从 MV5.Posts 中选择并插入到 MV6.Posts 中。插入时，我在 MV6.Posts 中检索新行的 ID，并在旧的 MV5.Posts.MV6ID 字段中更新它。

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.

有没有办法通过 INSERT INTO SELECT FROM 执行此更新，这样我就不必手动处理每条记录？我使用的是 SQL Server 2005 开发版。

采纳答案by HLGEM

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.

通过运行测试用例并将数据与迁移前存储的数据进行比较来测试迁移。彻底测试迁移数据至关重要，否则您无法确定数据是否与旧结构一致。迁移是一项非常复杂的行动；花时间非常有条理和彻底地完成它是值得的。

回答by Brann

AFAIK, you cannot update two different tables with a single sql statement

AFAIK，你不能用一个 sql 语句更新两个不同的表

You can however use triggers to achieve what you want to do.

但是，您可以使用触发器来实现您想要做的事情。

回答by JoshBerke

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

我所知道的最好的方法是使用output 子句。假设您有 SQL 2005 或 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.

它仍然需要第二遍来更新原始表；但是，它可能有助于使您的逻辑更简单。是否需要更新源表？您可以将新 ID 存储在第三个交叉引用表中。

回答by tpdi

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.

将 old_id 放在新表中使更新更容易——你可以只做一个insert into newtable select ... from oldtable, -- 和随后的记录“拼接”更容易。在“缝合”中，您将通过在新的父级 ( insert into newchild select ... (select id from new_parent where old_id = oldchild.fk) as fk, ... from oldchild)上执行子选择来更新插入中子表的外键，或者您将插入子表并进行单独的更新以修复外键。

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_idcolumns, 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.

迁移后，您可以删除old_id列，或者，如果您遇到遗留系统公开 id 并且用户将键用作数据的情况，您可以保留它们以允许基于 old_id 的使用查找。

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

事实上，如果您正确定义了外键，您可以使用 systables/information-schema 来生成您的插入语句。

回答by Joel

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:

可能最简单的方法是在 MV6.Posts 上为 oldId 添加一列，然后将旧表中的所有记录插入到新表中。最后，使用以下内容更新新表中 oldId 上匹配的旧表：

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.

如果您愿意，您可以在之后清理并删除 oldId 列。

回答by Christian Johansson

Make a column in MV6.Post.OldMV5Id

在 MV6.Post.OldMV5Id 中创建一列

make a insert into MV6.Post select .. from MV5.Post

插入 MV6.Post select .. from MV5.Post

then make an update of MV5.Post.MV6ID

然后更新 MV5.Post.MV6ID

回答by dance2die

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

有没有办法通过 INSERT INTO SELECT FROM 执行此更新，这样我就不必手动处理每条记录？

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

既然你不想做手工，但自动，创建一个触发器MV6.Posts，使UPDATE发生在MV5.Posts当你插入自动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