TSQL：更新INSERT INTO选择 [英] 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字段显示在新的数据库及其相应的编号位置。

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做到这一点的更新不这样我就不必手动处理每一条记录？我使用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选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！