在 T-SQL 中克隆带外键约束的行

• 下载源代码 - 5.61 KB

引言

在 SQL 中复制一行很简单：select * into newTable from oldtable，以及其他变体。

但是，如果想要复制具有指向该行的外键的表呢？例如，假设你有一个 [users] 表，以及一个指向 [users] 的外键的 [user_preferences] 表。

在这里，我将向你展示如何在无需为想要复制的所有表编写代码的情况下做到这一点。

背景

为了使用这段代码，你需要一个特殊的聚合函数 strconcat。 你可以在 CLR 中轻松创建它；请查看 此页面 以获取更多详细信息。

使用代码

要使用此过程，请按如下方式调用它

declare @oldId bigint, @newId bigint 
set @oldId = 60000

--Clone the very first row manually, so we get a PK_ID
select *
    into #usersToClone
    from users
    where id= @oldId
alter table #usersToClone
    drop column id
insert into users
    select * from #usersToClone
set @newId = scope_identity()

--Include this table to exclude tables from being cloned
create table #table_ExcludeFromCloning(
    tableName nvarchar(max)
)

--Actual clone routine
exec table_CloneChildrenOfRow 'users', 'dbo', @oldId, @newId

它的工作原理 - SQL 中的递归和其他技巧

为了自动查找外键表中的行，我们需要使用一些技巧。 以下是其中四个最大的技巧

1. 首先，我们需要获取指向表的那些外键。 幸运的是，SQL Server 有一个存储过程 sp_fkeys，我们可以用它来做到这一点。
2. 其次，一旦我们获得了该外键表列表，我们需要在每个表上执行 SQL。 在这里，我们使用 sp_executesql 来运行我们动态创建的 SQL。
3. 第三，我们的代码是递归 SQL - 我们有一个过程来复制指向该行的所有外键引用，另一个过程来复制其中一个外键表的行。 它们必须相互调用，因为外键表本身可能具有自己的主键！
4. 第四，我们有一个有趣的问题 - 如何将一个表从一个过程传递到另一个过程？ 表值参数会有所帮助，但它们仅在 SQL Server 2008 中可用。 在这种情况下，我们必须使用一些技巧，使用临时表和表变量。 请记住，表变量仅在当前过程的范围内具有作用域，而临时表在整个调用堆栈中具有作用域 - 我们必须同时使用两者！

关注点

这段代码适用于具有多个外键表引用它们的简单表，但某些奇怪的情况（例如循环引用）会破坏它。

如果你有幸使用 ORM，我建议首先探索它，然后再采用这种方法。 :)

如有请求和更新，请与我联系！

有什么问题吗？ 我只在我们的本地环境测试过这段代码。 请通过 Gmail 联系我 chris.magradar。

历史

• 最新更新 - 2010 年 4 月 16 日 - 新版本增加了对不同表所有者的支持。
• 第一个版本 - 2010 年 3 月 30 日。