如何将其他表中具有相关记录的 SQL 记录复制到同一个数据库中？
我在 C# 中创建了一个函数，它允许我将记录及其相关子项复制到同一数据库中的新记录和新相关子项。 （这是一个允许使用以前的工作作为新工作模板的应用程序。）
无论如何，它工作得很好...以下是它如何完成复制的描述：
它填充一个两列的基于内存的外观 - up 表中每条记录的当前主键。接下来，当它单独创建每个新的副本记录时，它会使用新记录的 Identity PK [从 SCOPE_IDENTITY() 检索] 更新查找表。现在，当它复制任何相关子项时，它可以查找新的父项 PK 以在新记录上设置 FK。
在测试中，在 SQL Server 2005 Express Edition 的本地实例上复制关系结构只需要一分钟。不幸的是，事实证明它的生产速度非常慢！我的用户正在通过 LAN 到我们的 SQL Server 处理每个父记录 60,000 多条记录！虽然我的复制功能仍然有效，但每条记录都代表一个单独的 SQL UPDATE 命令，并且它以大约 17% 的 CPU 负载加载 SQL Server，而正常空闲率为 2%。我刚刚测试完5万条记录副本，花了将近20分钟！
有没有办法在 SQL 查询或存储过程中复制此功能，以使 SQL 服务器完成所有复制工作，而不是从每个客户端通过 LAN 进行爆炸？
（我们正在运行 Microsoft SQL Server 2005 标准版。）
谢谢！
-德里克
I created a function in C# which allows me to copy a record and its related children to a new record and new related children in the same database. (This is for an application that allows the use of previous work as a template for new work.)
Anyway, it works great... Here's a description of how it accomplishes the copy:
It populates a two-column memory-based look-up table with the current primary key of each record. Next, as it individually creates each new copy record, it updates the look-up table with the Identity PK of the new record [retrieved from SCOPE_IDENTITY()]. Now, when it copies over any related children, it can look up the new parent PK to set the FK on the new record.
In testing, it only took a minute to copy a relational structure on a local instance of SQL Server 2005 Express Edition. Unfortunately it is proving to be horribly slow in production! My users are dealing with 60,000+ records per parent record over the LAN to our SQL Server! While my copy function still works, each of those records represents an individual SQL UPDATE command and it loads the SQL Server at about 17% CPU from its normal 2% idle. I just finished testing a 50,000 record copy and it took almost 20 minutes!
Is there a way to duplicate this functionality in SQL queries or stored procecures to make the SQL server do all of the copy work instead of blasting it over the LAN from each client?
(We're running Microsoft SQL Server 2005 Standard Edition.)
Thanks!
-Derek
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的，如果是 SQL 2005，您可以使用 CLR 存储过程将 C# 代码安装到数据库中。要使用纯 TSQL 来完成此操作，只是比较繁琐并且需要使用变量作为新键。这是否只是一个父记录和仅在一个子表中的许多子记录的情况？在这种情况下，您可以通过利用可以将 select 语句嵌入到插入中的事实来避免为每个子行发出单独的插入命令，特别是当您使用 PK 的标识列时。如果你能做到的话，通常会快得多。
像这样的东西（语法可能有点偏离，但无论如何你都需要将其适应你的真实表）：
这是 SQL Server 中 CLR 的起点...
http://msdn.microsoft.com/en-us/library/ms131045.aspx（本文是 SQL 2008，但其中大部分可能适用）。
http:// www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1
Yes there is, if it's SQL 2005, you can potentially use a CLR stored procedure to install your C# code into the Db. To do it pure TSQL, it's just a matter of it being tedious and needing to use variables for the new keys. Is this just a case of one parent record and then a lot of child records that are only in one child table? You could potentially avoid issuing separate insert commands for each child row in this case by leveraging the fact that you can embed a select statement into an insert, especially if you're using identity columns for the PKs. That will usually be MUCH faster if you can do it.
Something like this (syntax may be a bit off, but you'll need to adapt it to your real tables anyhow):
Here's a starting point on CLR in SQL Server...
http://msdn.microsoft.com/en-us/library/ms131045.aspx (This article is SQL 2008, but much of it probably applies).
http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1
使用（伪代码）
对相关子表有帮助吗，因为它们将被批量更新而不是每条记录单个更新？
Would using (pseudo code)
help for the related child tables in that they will be batched rather than single updates per record?