SQL Server Copy Tables

A copy table means making the clone or duplicate table of an existing table. The copy table must include the same table structure, indexes, constraints, and default values as of original table. Copying data of an existing table into a new table is very useful in a situation like backing up data in table failure. It's also useful in situations like maintenance, testing, demonstration, and moving to a new instance without affecting the original table.

This article will explain multiple ways about how we can make a duplicate table in SQL Server from one database to another. The following are the popular methods to make a duplicate table:

• Using SQL Query
• Using SQL Server Management Studio (SSMS)
• Using Generate Scripts

Using SQL Query

We may use the SELECT INTO statement to copy the table from one database to another or within the same database. This statement will first create a new table before copying the data to them. If we successfully copy database objects like indexes and constraints, we must create a query for each one and then add the query to the destination database.

The following are the syntax that illustrates the SELECT INTO statement:

Example

Let us understand how we can use the SELECT INTO statement to make a clone table. Suppose we have a table 'employee' that contains the following data:

If we want to make a duplicate table of the employee table by copying all records as the backup_employee table, we can use the SELECT INTO statement as follows:

The clone table contains the columns in the same order as specified in the SELECT statement. These columns have the same name, data type, nullability attribute, and value as of original table. If an Identity column exists in any of the tables, the new column in the clone table will inherit this property without the need to enable Identity_Insert.

We can verify whether employee data is successfully inserted into the backup_employee table as follows:

Here is the output:

Using SQL Server Management Studio

We can also use the Management Studio to make a clone table or copy source table data into the destination table. We can do this by using the SQL Server Management Studio's Export and Import wizard. To transfer/copy data, the user may choose to export from the source database or import from the destination database using this wizard.

The following are the steps used to make clone tables from one database to another:

Step 1: Open SQL Server Management Studio and navigate to the object explorer.

Step 2: Inside the object explorer, select Databases and expand it to see the available database.

Step 3: Select the desired database, right-click on it. A context menu will open, click on the Tasks option and select the Export Data command as shown below:

Step 4: We will get the "Welcome to SQL Server Import and Export Wizard", click on the Next button.

Step 5: In the next step, we will see the 'Choose a Data Source' window. Here, we need to specify the source Server Name, an Authentication method that connects to the source server, the source database name, and then click the Next button. See the below image:

Step 6: In the next step, we will see the 'Choose a Destination' wizard. Here, we will specify the destination Server name, the Authentication method to connect with the destination server, the destination Database name, and then click the Next button. See the below image:

NOTE: We are going to use the same source and destination database and server.

Step 7: In the next step, the Specify Table Copy or Query wizard will open. Here, we need to select the Copy data from one or more tables or views option and click the Next button:

Step 8: This step will open the Select Source Tables and Views wizard. Here, we need to choose the tables you want to copy from the source to the destination one and then click the Next button.

Step 9: We need to click on the Edit Mapping button and tick the Create destination table option to ensure that the selected tables will be created in the destination database. If any of the selected tables have an identity column, we must also tick the Enable identity insert option before clicking the OK button. If we selected multiple tables to copy to the destination database, we should click on the Edit Mappings option again and check each table one by one.

Now, we will click the Next button when all tables are checked via the Edit Mappings.

Step 10: This step will open the Save and Run Package wizard. Here, we need to click the Next button.

Step 11: This step will open the Complete the Wizard step. Here, we need to click the Finish button.

Step 12: When all the execution is completed successfully, we will see the steps taken to copy the tables and the number of records transferred. Here, we need to review the steps and the messages and then press the Close button if there are no errors:

This method is the fastest way to make a clone table, but it will fail to transfer or copy the table's indexes and keys. If we want to make a clone table with indexes and keys, we are required to use the Generate Scripts method for these database objects. If the tables we are going to make clone are foreign key connecting, we must export the data in the correct order. If we do not do this, the export wizard will fail.

Using Generate Scripts

The Generate Scripts method is another way provided by the SQL Server. This method is used when we want to not only copy the table schema and data from the source database to destination one but also objects, indexes, triggers, constraints, keys, etc.

We can use the following steps to generate a script to make clone tables from one database to another:

Step 1: Open SQL Server Management Studio and navigate to the object explorer.

Step 2: Inside the object explorer, select Databases and expand it to see the available database.

Step 3: Select the desired database, right-click on it. A context menu will open, click on the Tasks option and select the Generate Scripts command as shown below:

Step 4: The next step will open the Choose Objects wizard. Here, we will choose the Select specific database objects option that displays the tables for which we will generate the script. Select the desired tables by marking corresponding to each one and then click the Next button:

Step 5: This step will open the Set Scripting Options page. Here we will specify the path where we want to save the generated script file and then click the Advanced button:

When we click the 'Advanced' option, we will see the Advanced Scripting Options page. Here we will choose the Types of data to script and specify 'Schema and data' as shown below:

This page also enables us to decide to script the indexes and keys in our tables. After filling necessary details on this page, click on the OK button. We will again navigate back to the Advanced Scripting Options page, click the Next button.

Step 6: This step will open the Summary page, where we will review the entire process step and click the Next button:

Step 7: The next step will open the Save Scripts page, where we can monitor the progress of the entire process. If everything is correct, click on the Finish button. Now, we can find the script at the specified location and execute it in the desired database.

The Scripting method in SQL Server is helpful for generating a single script for the schema and data of the tables and indexes, and keys. However, if there are relationships between the tables, this method does not correctly produce the tables' creation script.

What to do when the database file is corrupted or damaged?

When we start any process or task that involves a SQL Server database, there might be a chance that data will be lost or the SQL Server database file will be corrupted due to wrong or incorrect execution or poor database file handling. Kernel for SQL Database Recovery is a SQL recovery tool that can be used when the SQL Server database file is corrupted or damaged. It can easily restore corrupted, damaged, or inactive MDF & NDF files while maintaining the original file structure and properties.

Conclusion

This article explains the several methods to make clone tables from the source database to the destination place along with indexes and keys. These methods are easy but require a big effort to copy the table, including indexes and keys. If the specified table has relationships, these tools cannot handle the creation order of tables. Furthermore, if anything goes wrong during the process, there might be a risk of being corrupted and damaged of both the data and the database file.