Copy from one database table to another C#

Question

Using C# (vs2005) I need to copy a table from one database to another. Both database engines are SQL Server 2005. For the remote database, the source, I only have execute access to a stored procedure to get the data I need to bring locally.

The local database I have more control over as it's used by the [asp.net] application which needs a local copy of this remote table. We would like it local for easier lookup and joins with other tables, etc.

Could you please explain to me an efficient method of copying this data to our local database.

The local table can be created with the same schema as the remote one, if it makes things simpler. The remote table has 9 columns, none of which are identity columns. There are approximately 5400 rows in the remote table, and this number grows by about 200 a year. So not a quickly changing table.

Answer 1

Perhaps SqlBulkCopy; use SqlCommand.ExecuteReader to get the reader that you use in the call to SqlBulkCopy.WriteToServer. This is the same as bulk-insert, so very quick. It should look something like (untested);

using (SqlConnection connSource = new SqlConnection(csSource))
using (SqlCommand cmd = connSource.CreateCommand())
using (SqlBulkCopy bcp = new SqlBulkCopy(csDest))
{
    bcp.DestinationTableName = "SomeTable";
    cmd.CommandText = "myproc";
    cmd.CommandType = CommandType.StoredProcedure;
    connSource.Open();
    using(SqlDataReader reader = cmd.ExecuteReader())
    {
        bcp.WriteToServer(reader);
    }
}

Answer 2

Bulk Copy feature of ADO.NET might help you take a look at that :

MSDN - Multiple Bulk Copy Operations (ADO.NET)

An example article