How To Duplicate A Table In SQL Server

Recently, we were required to set up a QA environment where we had to create all the tables as they were in the dev environment. So, as a developer on an SQL server, I analyzed the complete requirement and concluded that we should duplicate all the tables from the dev environment to the QA environment.

Approach-1: Using the SELECT INTO Statement

The best and most straightforward way to duplicate a table in an SQL server is to use the SELECT INTO Statement, which creates the exact copy of the table structure with data.

Syntax

SELECT * INTO TableName_Copy
FROM TableName

Example

The below query will create a duplicate Order table.

SELECT * INTO Order_Copy
FROM [Order]

After executing the above query, a duplicate of the Order table was successfully created, as shown in the screenshot below.

Check out How To Update Statistics On A Table In SQL Server

Now, if we execute the select query below, you can see we got the expected output, as shown below.

Select * from Order_Copy

Check out: How To Check If CDC Is Enabled On A Table In SQL Server

Approach-2: Using CREATE TABLE and then execute INSERT INTO statement

First, create table statement to create a new table.

CREATE TABLE Order_Clone
(
    OrderID INT,
    ProductID INT,
    OrderName nvarchar(50),
    OrderType nvarchar(50),
	TotalAmount INT
)

After executing the above query, the table was created successfully.

Now, let us execute the INSERT INTO statement to copy the data from the original table.

INSERT INTO Order_Clone
SELECT OrderID, ProductID, OrderName, OrderType, TotalAmount
FROM [Order]

After executing the above query, the data is copied from the Order table.

To cross-check if the data was copied successfully, let us execute the below select query.

Select * from Order_Clone

Check out: How To Rename The Table Name In SQL Server

Approach-3: Using SQL Server Management Studio

1. Open SSMS and right-click on the table name –> Script Table as –> CREATE To –> Select the New Query Editor Window option as shown in the screenshot below.

Modify the name of the table and then execute this query.

USE [Test]
GO

/****** Object:  Table [dbo].[Order]    Script Date: 06-10-2024 13:07:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Order_New](
	[OrderName] [nvarchar](50) NOT NULL,
	[OrderType] [nvarchar](50) NULL,
	[OrderID] [int] NULL,
	[ProductID] [int] NULL,
	[TotalAmount] [int] NULL
) ON [PRIMARY]
GO

After executing the above query, the new table was created successfully, as shown below.

2. Now, execute the below insert query to copy the data to the Order_new table.

INSERT INTO Order_New
SELECT OrderID, ProductID, OrderName, OrderType, TotalAmount
FROM [Order]

Conclusion

Duplicating tables in SQL Server is a common task that can be completed using various approaches. Based on your requirements, you can use the information in this article to complete this task.

You may also like following the articles below.

• How to Export SQL Server Table to CSV
• How To Check User Permissions On Table In SQL Server