Copy tables between databases in SQL Server

As a DBA, you might need to copy the objects and the content of specific tables from a database to another one in the same instance or in any different SQL instance. You might think of using Insert Into Select statement in MS SQL Server but it will not be useful in scenarios such as transferring few tables from a production database to a development one for testing or troubleshooting. Also, this depends on the number of tables, size, and available space in the database. If the total size of the tables is more than 50% of the total size of the database than the recommended method to use is the backup and restore the database.

To copy tables between databases in SQL Server by generating Scripts using SQL Server Management Studio follow the below steps –

  1. Connect the SQL Server instance, Open the Object Explorer and select the database.
  2. Right-click the database, select Tasks, and then click on Generate Scripts, Click on “Next”.


  3. On the Choose Object page, choose Script entire database and all database objects or Select specific database objects option and Click Next.


  4. For Set Scripting Options, Select the Output Type, choose the file destination and name an click Next.


  5. Now the Summary page details will appear for the entire process. Click Next.


  6. Now, the Save or Publish Scripts page shows the progress of the entire process as shown below, click Finish.



  7. The script will be available at the selected location and executed in the desired database.

    My Personal Notes arrow_drop_up

    Check out this Author's contributed articles.

    If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

    Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


    Article Tags :
    Practice Tags :


    Be the First to upvote.


    Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.