How to stream data rows into sqlserver?

Question

I need to copy large resultset from one database and save it to another database.

Stored procedures are used for both fetching and storing due to the fact that there is some logic involved during saving.

I'm trying to find an efficent solution, no way I can hold the whole dataset in memory, and I would like to minimize roundtrips count.

Data is read from source table with

var reader = fetchCommand.ExecuteReader();
while (reader.Read()){...}

Is there a way to insert this data to another sqlCommand without loading the whole dataset into a DataTable but also without inserting rows ine by one?

Sqlserver is MS SQL Server 2008 on both source and target databases. Databases are on different servers. Use of SSIS or linked servers is not an option.

EDIT: It appears it's possible to stream rows into a stored procedure using table-valued paramaters. Will investigate this approach as well.

UPDATE: Yes it's possible to stream data out from command.ExecuteReader to another command like this:

var reader = selectCommand.ExecuteReader();
insertCommand.Parameters.Add(
    new SqlParameter("@data", reader)
        {SqlDbType = SqlDbType.Structured}
    );

insertCommand.ExecuteNonQuery();

Where insertCommand is a stored procedure with table-valued parameter @data.

Answer 1

You need SqlBulkCopy. You can just use it like this:

using (var reader = fetchCommand.ExecuteReader())
using (var bulkCopy = new SqlBulkCopy(myOtherDatabaseConnection))
{
  bulkCopy.DestinationTableName = "...";
  bulkCopy.ColumnMappings = ...
  bulkCopy.WriteToServer(reader);
}

There is also a property to set the batch size. Something like 1000 rows might give you the best trade-off between memory usage and speed.

Although this doesn't let you pipe it into a stored procedure, the best approach might be to copy data to a temporary table and then run bulk update command on the server to copy the data into its final location. This usually faster by far than executing lots of separate statements for each row.