none
DB table copy RRS feed

  • Question

  • Hi,

    I have 2 databases in my application that have the same schema. I would like to programatically copy all records from 'Table1' of db1 to 'Table1' of db2. What is the fastest way to do that?

    Thank you in advance
    Friday, March 20, 2009 2:43 PM

Answers

  • If it is SQL Server and both databases located on same server, you could use SQL statement (similar to sample below) that would do this job for you:


    INSERT INTO Database2.dbo.MyTable SELECT * FROM Database1.dbo.MyTable

    In a case of different servers or different types of databases you could use SQL Server Integration Services that would allow to import/export data between different databases.


    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, March 24, 2009 10:24 AM
    Moderator

All replies

  • Hi there

    Not sure what you mean by programatically. T-SQL or client side code (.Net for example)?
    If you do use .Net, then there is a Copy method on the DataTable object.

    ".NET Framework Class Library - DataTable.Copy Method"
    http://msdn.microsoft.com/en-us/library/system.data.datatable.copy.aspx

    HTH
    //Michael
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, March 20, 2009 3:33 PM
  • If it is SQL Server and both databases located on same server, you could use SQL statement (similar to sample below) that would do this job for you:


    INSERT INTO Database2.dbo.MyTable SELECT * FROM Database1.dbo.MyTable

    In a case of different servers or different types of databases you could use SQL Server Integration Services that would allow to import/export data between different databases.


    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, March 24, 2009 10:24 AM
    Moderator
  • Or you can do this with SSIS (SQL Server Integration Services) in a few clicks
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Friday, March 27, 2009 3:57 AM
  • If your tables are very large you may want to try SqlBulkCopy(). It will ignore established indices which slow down inserts.
    Tuesday, March 31, 2009 6:15 PM