How To Copy One Table To Another In SQL Server

Due to certain maintenance activities on my client’s end, I recently encountered a requirement to copy one critical table to another in our SQL Server database. As we all know, this is a common task in database management, and several approaches exist depending on the specific needs and constraints. I will discuss all possible approaches for a successful migration in this article.

Approaches -1 Using SELECT INTO Statement

You can use the query below to copy the structure and data into a brand-new table.

SELECT * INTO NewTable FROM OldTable;

For example, I wish to create a new table, ProductNew, with the same structure as my existing table, Product with the existing data. So, let us execute the SQL query below.

SELECT * INTO ProductNew FROM Product;

Check out: How to truncate table in SQL Server.

The select query was executed successfully, as shown in the screenshot below.

Now, let’s execute the select query below to check whether the new table was created successfully with the data from the old table.

Select * from ProductNew;

We got the expected output, as shown in the screenshot below.

If we check the old table, it has the same data and structure as shown in the screenshot below.

Approach-2: Using Select into statement with certain conditions

Now, if we wish to copy only the structure and create a new table with the same structure as the old table without data, we can execute the query below.

SELECT * INTO ProductLatest FROM Product where 1 = 6;

After executing the above query, we got the expected output, as shown in the screenshot below. Only the structure is copied, with no data from the old table.

Check out: How to get table size in SQL Server

Approach-3: Using INSERT INTO statement

We can execute the query below if you wish to copy only the data to an existing table and don’t need to create a new one.

INSERT INTO ProductLatest SELECT * FROM Product;

The data was copied successfully after executing the above query, as shown in the screenshot below.

Approach-4: Using INSERT INTO statement for Identity column

If the table you are trying to copy the data to contains an Identity column, you won’t be able to insert any explicit value, so you need to run the query in the order below. Suppose the ProductLatest table has the ProductID as an Identity column.

If we try running the above insert query, we will get the error shown in the screenshot below.

So, to fix this error, first, run the below query to set the IDENTITY_INSERT to ON.

Set IDENTITY_INSERT ProductLatest ON;

Check out How To Add A Column To A Table In SQL Server

Now, run the below Insert query.

INSERT INTO ProductLatest SELECT * FROM Product;

After executing the above query, I got the expected output below.

Now, run the below query to set the IDENTITY_INSERT to OFF.

Conclusion

Copying data from one table to another in SQL Server is a very common and essential activity. As mentioned in this article, various approaches can be used. You must decide on a method that fits your requirements and environment.

You may also like following the articles below.

• How to check if a table exists in SQL Server
• How To Check If Column Exists In SQL Server Database
• How To Change Column Name In SQL Server