使用链接服务器限制 SELECT 语句的行数
我有这些链接服务器，A和B。服务器A正在运行SQL Server 2008，而B正在运行SQL Server 2000。
我编写了一个脚本来将一堆表中的数据从B迁移到A。数据非常敏感，所以恐怕我不能再告诉你了。不过，我可以告诉您，我们正在讨论大量数据，最坏的情况是每个 SELECT 大约有 30 万行。我有点担心内存中保存的数据量太多。
我的第一个想法是，我可以使用 ROWCOUNT 来限制 SELECT，并循环遍历语句，直到传输所有行。但后来我在某处读到，你不能在链接服务器上使用该技术，但我现在似乎找不到对此的引用。
所以，我的问题有两个：
a) 在处理链接服务器时，是否真的不能使用上述技术？ （如果我的 PM 允许，我会自己测试）。
b) 如果 a 的答案是肯定的，关于如何保证两台服务器都能处理迁移有什么想法吗？
谢谢！
I have these linked servers, A and B. Server A is running SQL Server 2008, and B is running SQL Server 2000.
I have written a script to migrate data from a bunch of tables from B to A. The data is quite sensitive so I'm afraid I can't tell you anymore about that. I can, however, tell you that we are talking about a lot of data, worst case around 300k rows per SELECT. I'm getting a bit worried that the amount of data will be to much to hold in memory.
My first though was that I could use ROWCOUNT to limit the SELECT's and just loop over the statements until all rows were transferred. But then I read somewhere that you can't use that technique with linked servers, I can't seem to find a reference to that now though.
So, my question is twofold:
a) Is it true that you can't use the technique mentioned above when dealing with linked servers? (I'd test it myself if my PM would allow it).
b) If the answer to a is yes, any ideas on how to guarantee that both servers can handle the migration?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果我理解正确的话，您每次选择都会执行 300K，但您要迁移的整个表要大得多。如果是这种情况，您可能需要考虑
If I understand correctly, you're doing 300K per select, but the whole table you're migrating is much larger. If that's the case, you might want to consider one of the Bulk Import/Export options available in SQL Server.
我终于让我的产品经理批准使用两台服务器的克隆版本进行一些实验。事实证明，在内存中保留如此多的行数似乎无法与 SQL Server 相比。
I finally got my PM to give the green light for some experimentation using cloned versions of the two servers. It turns out that keeping that amount of rows in memory seems to be no match for SQL Server.