How to pass data rows to sqlserver?

Question

I need to copy a large set of results from one database and save it in another database.

Stored procedures are used for both retrieval and storage due to the fact that during storage there is some logic.

I am trying to find an effective solution, I cannot store the entire data set in memory, and I would like to minimize the number of visits.

Data is read from the source table using

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

Is there a way to paste this data into another sqlCommand without loading the entire data set into a DataTable , but also without inserting ine rows by one?

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

EDIT: It appears that the string flows into the stored procedure can be used using table parameters . Will also explore this approach.

UPDATE: Yes, it is possible to transfer data from command.ExecuteReader to another command as follows:

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

Where insertCommand is the stored procedure with the @data table @data .

Answer 1

You need SqlBulkCopy . You can simply 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 the property of setting the batch size. Something like 1000 lines can give you a better compromise between memory usage and speed.

Although this does not allow you to connect it to a stored procedure, the best way would be to copy the data to a temporary table and then run the bulk update command on the server to copy the data to its final location. This is usually faster than executing many separate statements for each line.

Answer 2

You can use SqlBulkCopy with a data reader that does roughly what you request (unbuffered, etc.), this will not call stored procedures for insertion. If you want this, you can probably use SqlBulkCopy to enter data into the second table (the same structure), and then loop the rows that invoke sproc locally on the database server. So latency etc. It ceases to be a problem (since the whole cycle is on the database server).