How can I copy data records between two instances of an SQLServer database

Question

I need to programmatically (ADO.Net) copy records from a table in one database to a table in another database on a different server. This is very similar to "How can I copy data records between two instances of an SQLServer database" except that I am not allowed to create a link to the destination server so the accepted answer to that question won't work for me.

Answer 1

You can use the SqlBulkCopy class

Answer 2

The SqlBulkCopy class suggested by santiiii is very efficient but it creates a non-logged operation. I had to do this once but my target database participated in replication, so I needed the operation to be fully logged. What I essentially ended up doing was selecting a dataset from the source database .

Select * from SourceDatabaseTable where (some clause to get the right records)

Then creating an empty dataset from the destination table with this statement

Select * from DestinationDatabaseTable where 1<>1

Then I had two datasets. The first with the records I wanted to copy and the second that is empty. Next I just did a nested foreach loop to copy the records from one dataset to the other. Here is the Pseudocode for the core copy function:

foreach(datarow sourcedr in sourcetable)
{
   datarow destdr = destdatatable.createrow();
   foreach(datacolumn in sourcedatatable)
   {
      destdr[datacolumn]=Sourcedr[datacolum];
   }
}

Lastly, I just used a data adapter to submit the changes on the destination database.

Answer 3

Here's how I did it. Thanks to the other respondants for the inspiration. The code that builds the mappings is not necessary if the schemas of the two tables are identical.

public void CopyTables(string sourceConnectionString, string destConnectionString)
{
        string sql = "Select * From SourceTable";
        using (SqlConnection sourceConn = new SqlConnection(sourceConnectionString))
        using (SqlCommand sourceCmd = new SqlCommand(sql, sourceConn)) {
            sourceConn.Open();

            using (SqlDataReader reader = sourceCmd.ExecuteReader())
            using (SqlBulkCopy copier = new SqlBulkCopy(destConnectionString)) {
                copier.DestinationTableName = "DestinationTable";
                copier.BulkCopyTimeout = 300;

                DataTable schema = reader.GetSchemaTable();
                copier.ColumnMappings.Clear();
                foreach (DataRow row in schema.Rows) {
                    copier.ColumnMappings.Add(row["ColumnName"].ToString(), row["ColumnName"].ToString());
                }

                copier.WriteToServer(reader);
            }
        }
    }
}