TSQL:使用 INSERT INTO SELECT FROM 进行更新 [英] TSQL: UPDATE with INSERT INTO SELECT FROM

问题描述

所以我有一个旧数据库，我正在迁移到一个新数据库.新的架构略有不同，但大多兼容.此外，我想从零开始重新编号所有表.

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.

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

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.

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

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.

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

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.

推荐答案

迁移的关键是做几件事:首先，不要在没有当前备份的情况下做任何事情.其次，如果键将发生变化，您需要至少暂时将新旧都存储在新结构中(如果键字段永久暴露给用户，因为他们可能正在通过它进行搜索以获取旧记录).

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.

这篇关于TSQL:使用 INSERT INTO SELECT FROM 进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！