How to Duplicate Table in SQL Server

Sometimes you may need to copy table data or structure, or both in SQL Server. In this article, we will look at how to duplicate table in SQL Server.

How to Duplicate Table in SQL Server

There are different ways to duplicate table in SQL Server using SELECT…INTO… statement. We will look at each of them one by one.

Duplicate Table Structure & Data

Let us say you have products table and want to copy data to another table new_products.

# select * from products;
+------------+--------------------+-------+
| product_id | product_name       | price |
+------------+--------------------+-------+
|          1 | iPhone 11          |   400 |
|          2 | Samsung Galaxy A50 |   250 |
+------------+--------------------+-------+

Also read : How to Truncate Table in SQL Server

Here is the SQL query to copy data and structure from one table to another. Replace source_table & destination_table below as per your requirement.

# select * from destination_table
  from source_table;

Here is the SQL query to copy data and structure from products to new_products

# select * into new_products 
  from products;

# select * from new_products;
+------------+--------------------+-------+
| product_id | product_name       | price |
+------------+--------------------+-------+
|          1 | iPhone 11          |   400 |
|          2 | Samsung Galaxy A50 |   250 |
+------------+--------------------+-------+

Also read : How to Rename Table in SQL Server

Copy Specific Columns

If you want to copy only specific columns from one table to another, then you need to specify those columns in your select statement.

# select product_id, product_name
  into new_products
  from products;

# select * from new_products;
+------------+--------------------+
| product_id | product_name       |
+------------+--------------------+
|          1 | iPhone 11          |
|          2 | Samsung Galaxy A50 |
+------------+--------------------+

Also read : How to Insert Data from Another Table in SQL Server

Copy Only Table Structure

If you only want to copy table structure and not data, use the following SQL query.

# select *
  into new_products
  from products
  where 1=0;

In the above SQL query, no rows are copied due to WHERE clause but only the structure is copied.