Limiting rowcount of SELECT statements, with linked servers

Question

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!

Answer 1

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.