How to copy SQL tables from one database to another?

Read time 8 minutes

The need for copying tables from one database to another can appear in the circumstances like maintenance, testing, demonstration, migration, transfer to a different instance, and more. Doing so is easy and can be done via multiple ways as SQL Server offers several methods for the same.

People experienced with SQL queries can execute the same operation in SQL Server with no problems at all, but those who hold less knowledge regarding SQL queries and Transact-SQL may find the task difficult.

In this article, we’ll guide you through how to copy tables from one database to another in SQL Server using different methods available and discuss a little on SQL Server database file corruption and how to repair corrupt SQL database. SQL administrators will have to do all these tasks now or then to keep their databases healthy and to work always.

To copy tables, we need access to specific databases – the source server and the destination server. Here,

Source Database: TechForums19
Destination Database: TechForums20

Copying tables using query

This method makes utilization of SELECT INTO query.

Select * into TechForums20.userforum.user from TechForums19.userforum.user

Above query copies only the table schema and data, and in case if you wish to copy objects, indexes, triggers, and constraints then, doing so is not possible with SQL queries/commands. We’ll discuss later in this article that how can you copy the rest of the database items.

Using SQL server management studio

The second method for copying tables in SQL Server is the Export and Import wizard, available in SQL Server Management Studio. In this option, the user holds the choice either to import from the destination database or to export from the source database to transfer/copy the data.

Follow below steps to copy tables from one database to another in SQL Server:

1. Open SQL Server Management Studio.
2. Inside the object explorer, right-click on TechForums19 database > Tasks > select the Export Data command.
3. In this step, specify the Server Name, Authentication method, and the Source database name, and click Next.
   

   Note: For this guide, TechForums19 is the source database name as specified above. Change the source database name with yours and mention the server name & authentication method.

4. In this step, specify the Destination database name and click Next.
   

   Note: Again, mention the Server name and authentication method in this step as well.

5. Select Copy data from one or more tables or views and click Next.
   
6. Here, the Select Source Tables and Views wizard will pop on-screen; select the Tables you want to copy from source database to destination database, as shown below, and click Next.
   

   Note:
   To make sure, that the tables you selected will be created in the destination database, click on the Edit Mappings button and tick the Create destination table option.

   In case, if the tables contain the identity column, tick the Enable identity insert option, and click Ok button.

   If selected more than one table to copy to the destination database, click on Edit Mappings again, and check for all tables one after the other.

   

7. After checking all the tables via Edit Mappings, click Next in the Select Source Tables and Views.
8. Save and Run wizard would open in this step; click on the Next button.
   
9. Click Finish.
   

   Note: Make sure to specify correct names of both Source & Destination databases, to transfer tables from one database to another.

   

   Copying tables SQL Server using the SQL Server Management Studio is a quick way of completing the job, but it fails to transfer/copy the table’s indexes and keys.

   And if you want to copy the table indexes & keys, you’ll have to make use of the Generate Scripts method.

Using generate scripts

This method enables you to copy not only the table schema and data but also objects, indexes, trigger, constraints, keys, etc.

Go through the steps below to generate a script to fully copy tables from one database to another in SQL Server:

1. Open SQL Server.
2. Right-click on the database name > Tasks > Generate Scripts.
3. Script Wizard would open, click on Next button.
4. Select the Database you want to Generate Script for.
5. Select the Object types and click on Next.
6. Select the tables to copy.
7. Choose the specific Output option for the script.
8. Edit the database name with the name you want to execute the script for.
9. Done.

What to do when the database file turns corrupt or damaged?

When you initiate any task or job associated with SQL Server database, there remains a slight risk of data loss or SQL Server database file corruption either due to wrong or incorrect execution or bad handling of the database file. There are critical situations that result in frustration as getting data back from the inaccessible MDF/NDF database file becomes almost impossible.

In scenarios of SQL Server Database file corruption or damage, we recommend using a SQL recovery .

This tool enables you to seamlessly, repair the corrupt, damaged, inaccessible MDF & NDF files with ease while maintaining the original file structure & properties. It also allows you to recover MDF files after a ransomware attack. With this tool, you can recover SQL all objects like tables, triggers, functions, rules, deleted records, etc.

It provides the user with full control over database objects and sets no restriction over the file size.

Additionally, we’ve made it compatible with a wide range of Microsoft SQL Server versions starting from SQL Server 2000 to 2019, so no matter what version of SQL Server you’re running on your system, it’ll be ready to use once the installation is finished.

Wrap

Transferring or copying tables from one database to another in SQL Server is easy, but some methods are long and quite complex, and if something goes wrong during the process, both the data and the database file will be at high risk of file corruption & damage. To fight with such situations, try Kernel for SQL Database Recovery, the ace third-party SQL recovery tool for SQL administrators. It supports all SQL versions, including SQL Server 2019.