none
How to Move SQL tables from one database to another?

    Question

  • Hello All,

    I have two databases say one in dev and one in prd. I have created the tables in dev and now I would like to create the same tables in prd. Instead of creating the tables again manually in prd I would like to do it through a query. Can you please help me doing this.

    Example: DBDEV(copy from)

                  DBPRD (copy to)

    Note: I do not want the original tables in DBDEV to be deleted.

    Thanks,

     


    Farooq Jiwani Microsoft E-Learning www.microsoft.com/elearning
    • Edited by FJ2008 Thursday, July 01, 2010 2:42 PM not clear
    Thursday, July 01, 2010 2:28 PM

Answers

  • Should have asked in T-SQL forum. slightly off topic with the theme of this forum

    a) Use import export wizard, it is a wizard steps are explains by BOL 

    http://msdn.microsoft.com/en-us/library/ms140052.aspx

    B)

    another option is to use Generate Script Wizard, starting with SQL 2008 there is an option to also script the data.

    Right click on your database-->Tasks-->Select Database --> Under table/view option make script data to true -->

    now run the generated script in prod.  

    c) T-SQL  (this does not copy constraints)

        SELECT * FROM DEVServer.Schema.TableName
        INTO ProdServer.Schema.TableName

    d)   If you are familiar with SSIS - transfer sql server objects

    • Edited by Chirag Shah Thursday, July 01, 2010 3:13 PM additional info
    • Marked as answer by FJ2008 Thursday, July 01, 2010 3:34 PM
    Thursday, July 01, 2010 3:04 PM

All replies

  • Should have asked in T-SQL forum. slightly off topic with the theme of this forum

    a) Use import export wizard, it is a wizard steps are explains by BOL 

    http://msdn.microsoft.com/en-us/library/ms140052.aspx

    B)

    another option is to use Generate Script Wizard, starting with SQL 2008 there is an option to also script the data.

    Right click on your database-->Tasks-->Select Database --> Under table/view option make script data to true -->

    now run the generated script in prod.  

    c) T-SQL  (this does not copy constraints)

        SELECT * FROM DEVServer.Schema.TableName
        INTO ProdServer.Schema.TableName

    d)   If you are familiar with SSIS - transfer sql server objects

    • Edited by Chirag Shah Thursday, July 01, 2010 3:13 PM additional info
    • Marked as answer by FJ2008 Thursday, July 01, 2010 3:34 PM
    Thursday, July 01, 2010 3:04 PM
  • Thanks for awesome help Chirag. I used the first option and it did work the way I desired.

    This place is too good for me :)

     


    Farooq Jiwani Microsoft E-Learning www.microsoft.com/elearning
    Thursday, July 01, 2010 3:34 PM