Limiting rowcount of SELECT statements, with linked servers

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 ser开发者_Go百科vers can handle the migration?

Thanks!

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.

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.