I have to programmatically (ADO.Internet) copy records from the table in a single database to some table in another database on the different server. This is much like "How can I copy data records between two instances of an SQLServer database" except that i'm not permitted to produce a connect to the destination server therefore the recognized response to that question will not work with me.

You should use the SqlBulkCopy class

The SqlBulkCopy class recommended by santiiii is extremely efficient however it produces a non-drenched operation. I needed to do that once but my target database took part in replication, and so i needed the operation to become fully drenched. Things I basically wound up doing was choosing a dataset in the source database .

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

Then creating a clear dataset in the destination table with this particular statement

Select * from DestinationDatabaseTable where 1<>1

I Quickly had two datasets. The very first using the records I needed copying and also the second that's empty. Next I simply did a nested foreach loop copying the records in one dataset towards the other. This is actually the Pseudocode for that core copy function:

foreach(datarow sourcedr in sourcetable)
   datarow destdr = destdatatable.createrow();
   foreach(datacolumn in sourcedatatable)

Lastly, I simply used an information adapter to submit the alterations around the destination database.

Here's the way i made it happen. Because of another respondants for that inspiration. The code that develops the mappings is not required when the schemas of these two tables are the same.

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)) {

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

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