locked
Using C# to copy tables from a database on one server to another in SQL RRS feed

  • Question

  • Could anyone recommend a way to copy a table from one database to another on different machines?  I'm thinking there may be a way using the SQL BULK INSERT command.

    Currently I can perform sql commands from my C# application I just don't know of a good way to copy the data.  At the moment I'm reading the information from one table into a DataView and then looping through every column of every row to create an INSERT command and then executing that to insert a row at a time.

    Does anyone know of a better way?

    Thanks in advance.
    Wednesday, August 19, 2009 3:19 PM

Answers

  • Have you tried using BCP functionality of SQL Server. This allows you to export data into a file and then import it into the destination database. As far as I know this is the fastest way to move data especially when there is a lot of data. Also this can be done programmatically. Check SQL Server Books online for details.

    thanks
    Sudarshan
    Development Lead , Microsoft
    Friday, August 21, 2009 3:39 AM
    Moderator

All replies

  • If you can do that manually (aka not by code), you can use SQL Server Management Studio's features (2005 and >). When you right click on a database in Management Studio, you have the ability to Export Data or Import Data.
    Then you can choose the destination and then source of datas in term of database and table...

    hope this helps !

    Guim7
    Thursday, August 20, 2009 3:47 PM
  • Have you tried using BCP functionality of SQL Server. This allows you to export data into a file and then import it into the destination database. As far as I know this is the fastest way to move data especially when there is a lot of data. Also this can be done programmatically. Check SQL Server Books online for details.

    thanks
    Sudarshan
    Development Lead , Microsoft
    Friday, August 21, 2009 3:39 AM
    Moderator
  • First of all fill your dataset. Then the code below should work for you as far as a BULK INSERT is concerned:

    SqlConnection sCon = new SqlConnection(@"Server=servername; integrated security=SSPI; Database=db;");
    System.Data.SqlClient.SqlBulkCopy bc = new SqlBulkCopy(sCon);
    bc.DestinationTableName = tblName;
    sCon.Open();
    bc.WriteToServer(ds.Tables[0]);
    bc.Close();
    sCon.Close();

    This will take your dataset and as long as the table has the same columns and suitable datatypes it will bulk paste it into the destination table.

    Thanks,
    David
    Tuesday, August 25, 2009 11:38 PM
  • Thanks for all the answers guys.  This is my first time using the forums so I appreciate the help.

    Ben
    Thursday, August 27, 2009 9:19 AM