CODEWITHSUNDEEP
c#sql-serversql-server-2008bulkinsert
zzandy
zzandy

Reputation: 2353

How to stream data rows into sqlserver?

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.

Upvotes: 4

Views: 2411

Answers (2)

Tim Rogers
Tim Rogers

Reputation: 21713

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.

Upvotes: 5

Marc Gravell
Marc Gravell

Reputation: 1062945

You can use SqlBulkCopy with a data-reader, which does roughly what you are asking (non-buffered etc) - however, this won't be calling stored procedures to insert. If you want that, perhaps use SqlBulkCopy to push the data into a second table (same structure), then at the DB server, loop over the rows calling the sproc locally. That way, latency etc ceases to be an issue (as the loop is all at the DB server).

Upvotes: 3

Related Questions