Optimized process of moving records in the range of 1 million to 10 million

What would you do if you had to massage data and move it from a database on one server to a database on another server?

Massage data was limited to using CONVERT or CAST. This process was called by a Data Loader in C#.NET. The SQL scripts were executed in SQL Server 2008.

Would you suggest this pro开发者_JAVA百科cess be done using SQLBulkCopy, LINQ to SQL or should this be only done using a INSERT........ SELECT in TSQL? The data could consist in the range of 1 million to 10 million rows.

I would appreciate your views on this process to verify an opitimized process on performing the above operation.

LINQ-to-SQL should be avoided here; it isn't optimised for this (it is aimed at individual objects/records - not bulk). A cross-db (and possibly linked-server) insert/select is possible, but I would be looking at bulk options. I suspect SSIS (ex DTS) might be of use here - it is pretty much designed for this. If you need a managed option, a data-reader from the source (ExecuteDataReader()) connected to SqlBulkCopy to the target will perform the same function as SSIS (using the same bulk protocol).

I have some issues like these before. I solve this issue by SQLBulkCopy. bcp is great in performance.

Looking on amount of data you're going to operate I would personaly choose ReplicationServices http://msdn.microsoft.com/en-us/library/ms151198.aspx and avoid programming solution , if it's possible.