Post reply

Copy data from one database to another

  • October 3, 2007 at 1:36 pm

    #375131

    Hi have two exact database,

    How can I copy data from one table from database x to another table in database y that have the exact colums.

  • October 3, 2007 at 1:54 pm

    #738802

    Assuming there are no identity columns and that both DBs are on the same server, this should work:

    INSERT INTO destinationDB..TableName

    SELECT *

    FROM sourceDB..TableName

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • October 3, 2007 at 2:07 pm

    #738807

    Or you can use DTS or SSIS wizard to copy data from one database to another

  • October 3, 2007 at 8:32 pm

    #738907

    If the databases are in the same instance, i recommend John Rowan's post

    "-=Still Learning=-"

    Lester Policarpio

  • October 4, 2007 at 6:07 am

    #739006

    what is DTS or SSIS wizard?

  • October 4, 2007 at 6:11 am

    #739009

    Another question:

    How do I copy a table from one database to another on the same server?

    Without losing the dependency and keys?

  • October 4, 2007 at 8:08 am

    #739095

    Not an expert on this, but here goes -

    Open QA and set it to the destination DB.

    Run a series of queries

    Select *

    Into Table_A

    from sourceDB.dbo.Table_A

    Select *

    Into Table_B

    from sourceDB.dbo.Table_B

    .....

    Select *

    Into Table_Z

    from sourceDB.dbo.Table_Z

    This will create the table and insert the data from the source database over to the new one. That means that all the keys, rows, values should be moved over.

    I prefer the route of generating the script to create the tables and have that make them - that way you are sure that the columns are exactly the same, then insert the data into the tables. It does take a bit longer to do though.

    Either way should work fine. If this is a clean database, just give it a try and see what happens if you are worried about dependencies. The worst case would be you have to delete the tables and start over.

  • October 4, 2007 at 8:49 am

    #739126

    Lionel2007,

    It looks like your scope is creeping a bit here. Can you post the table DDL for the tables you'd like to copy over. If there are data dependencies in other tables that need to be moved also, include the DDL please.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • October 11, 2007 at 5:22 am

    #741138

    Hello, I manage all my data moves with redgate SQL Data Compare tool. It will compare and move data for you. For just on or two tables I would use the posted INSERT INTO with a SELECT. Moving lots of data in lots of tables proves challenging. The good thing about the data compare tool is you can , without question, know all your data was moved EXACTLY as you asked because the data compare tool will compare both database after it has moved it and you will see no differences, If you do , you can look into it.

    Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply