使用变量替换标识列从数据库中编写出对象的脚本，以将对象复制到其他环境

Question

我有多个应用程序环境，如DEV，TEST，UAT，PROD。

我需要将一些对象从在 UAT 环境中创建的数据库复制到 PROD 环境中。对象存储在分布到多个表中的数据库中。大多数表将 PK 作为标识(自动生成(。我无权访问 PROD 数据库数据(通常是敏感数据(。

我需要的是生成用于插入对象的 SQL 脚本，该对象不保留 Id 的值，而是使用目标环境中分配给相关记录的 Id。

示例：假设对象 Order 由 [Order] 和 [OrderItem] 行列表组成。我需要在 [Order] 表中选择一个特定行，指定还应包括 [OrderItem] 中的相关行，并生成将为 [Order] 插入新行的脚本，获取分配 Order.Id 的值，将其保留在变量中并使用它来插入 [OrderItem] 行。这是一个微不足道的例子，我的对象分布到更多的表中，但概念是相同的。

有什么工具可以做到这一点吗？我尝试的所有脚本输出实用程序都保留了标识列的值。

Answer 1

我认为您需要编写自定义代码来实现首先加载到父表，然后根据SCOPE_IDENTITY()值加载到子表的需求。

相反，如果您没有大量的行，我建议您按照以下步骤操作：

1. 使用 SSIS 包或其他方式将数据从生产环境加载到另一个环境
2. 在子表中添加外键约束以具有更新级联

ALTER TABLE ChildTable ADD CONSTRAINT FK_OrderDetail_Order FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
ON UPDATE CASCADE

3. 现在，使用某些逻辑将标识值更新为另一个值

set identity_insert Order ON
UPDATE Order SET OrderID = OrderID + 1000000 -- Have some other logic for random generation
set identity_Insert Order OFF

现在，子表将自动使用新的 OrderID 进行更新。

更新此 SO 链接讨论如何自动生成 INSERT 脚本的代码：为 SQL Server 表自动生成 INSERT 语句的最佳方法是什么？

另外，我建议您首先编写父表的脚本，然后是子表。

您可以使用以下脚本标识父表、子表。您现在需要根据父项、子项生成脚本。

select object_name(parent_object_id) as childTable, object_name(referenced_object_id) as parentTable
from sys.foreign_keys
WHERE object_name(parent_object_id) IN 'Your comma separated list of tables'