How to Copy Table from One Database to Another in SQL Server

There are certain situations where you are requested to copy the schema and the content of specific tables from one database to another. Do not worry at all as SQL Server provides various methods to perform a table’s data and schema copy process.

People experienced with SQL queries can perform the same operations in SQL Server with no problems, but those who have little knowledge about SQL queries may find the task difficult. So, here in this article, I will guide you on how to copy table data from one database to another SQL Server. Let’s get started.

Techniques to Copy Tables from one Database to Another

To copy the tables, we need to access specific databases. The source database and the destination database are: 

Source Database: Achievement19

Destination Database: ArysonDemo

The tables to be copied from the source database to the destination are Department, Employee, EmployeeDepboxHistory and EmployeePayHistory under the HumanResources schema.

Method #1: Copy Database in SQL Server Using Query

Use the below SELECT INTO query:

Select * into ArysonDemo.HumanResources.Department from 

Achievement19.HumanResources.Department 

To copy the tables from the Achievement19 database to ArysonDemo, we can use the Select into SQL query. This statement will first create tables in the destination database; then it will copy the data in these tables.

If you manage to copy database objects such as indexes and constraints, then you need to prepare the script individually for this, after that you need to apply the script to the destination database.

Method #2: Use SQL Server Management Studio to Copying Table from One DB to Another

The next method to copy table data from one database to another SQL Server is the Export and Import wizard that is available in SQL Server Management Studio. 

Here, you have the option to either import from the destination database or to export from the source database to copy or transfer the data. Follow the below steps: 

• Firstly, open SQL Server Management Studio.
• Under the object explorer, right-click on Achievement19 database > Tasks > choose the Export Data command.
• Next, specify the Server Name, Authentication method, and the Source database name. Click on the Next button.

Note: Here, Achievement19 is the source database name as mentioned above. Replace the source database name and specify the server name and authentication method.

• After that, specify the Destination database name and click on Next.

Note: Again, you have to specify the server name and authentication method in this step.

• Now, select Copy data from one or more tables or views and click on the Next button.

• Select Source Tables and Views wizard will appear on the screen; choose the Tables you wish to transfer from source database to destination database and click Next.

Note: To ensure that the tables you have selected will be created in the destination database, click the Edit Mappings button and check the Create Destination Table option.

If the tables contain the identity column, click the Enable identity insert option and hit on the Ok button.

If you have selected to copy more than one table to the destination database, then once again click on Edit Mappings, and check for all tables simultaneously.

• After that, click Next in the Select Source Tables and Views.
• Then, Save and Run wizard will pop-up, click on the Next button.

• Next, click on the Finish button.

Note: Keep in mind to provide correct names of both Source & Destination databases, to copy tables from one database to another.

What to do if Database files get Corrupt?

When you are copying tables from SQL Server, there are chances of data loss or SQL Server database file corruption. It happens because of the incorrect execution or bad handling the database file. 

In such a scenario, I would recommend using a SQL recovery tool. There are different tools available in the market, but you must go for Aryson SQL Database Recovery. This software enables you to seamlessly recover corrupt and damaged SQL database files with great ease. 

It recovers the database files while maintaining the integrity of the data. Additionally, the utility restores SQL Server database file and save in a new MDF file.

Final Words

Above, the methods to copy table data from one database to another in SQL Server has been described in detail. But some methods are quite lengthy and complicated, and if something wrong happens during the process, then the database file will quickly be at a high risk of file corruption. 

Therefore, you should make use of the SQL Database Recovery tool to tackle such situations. I hope it helps you out to resolve your query.

Realted Post