Copy Table from one SQL Server to another - Can't link them

I have two SQL servers: MyServer and TheirServer. I do not have the option of linking these two servers.

I would like to get all of the data from a table on TheirServer and copy it into MyServer on a daily basis. The current number of records in the table is over 600k and will slowly increase. The tables are identical to each other, column names and data types.

My initial thought was to try and f开发者_StackOverflow中文版ill a datatable with a Select * query against TheirServer, and then try a SQLBulkCopy into MyServer. Naturally, this causes a System.OutOfMemoryException on the web server. Using that train of thought, my next plan of attack is to use a SQLDataReader to pull around 50k records at a time, then pause reading and BulkCopy those records, rinse and repeat. Before I did that, I thought I would see if I'm going about this the wrong way.

Note: Since I'm just asking for a best practice, I don't mind if the code is in C# or VB, or if there is code at all.

The command line (shell) is your friend. Checkout the bcp utility.

Usage for your scenario is pretty simple:

bcp src-database.dbo.src-table out "c:\work\datafile.dat" -S srcSQLServerInstance -E -n
bcp tgt-database.dbo.tgt-table int "c:\work\datafile.dat" -S tgtSQLServerInstance -E -n

You'll need to truncate the destination (target) table prior to loading in the new data drop.

This can all be done in a *.cmd batch file and scheduled.

Alternatively, you can write an SSIS or DTS package to accomplish the same thing and schedule it within SQL Server.

Myself, I like having a script than can easily be debugged/tested.