Sql Server - Create a copy of a database table and place it in the same database?

sql

data-duplication

sql-server

database-management

byNikita Barsukov·Mar 7, 2025

In context of SQL Server, execute the following to duplicate a table:

SELECT * INTO CopyOfTable FROM SourceTable;

To clone the table's structure sans data:

SELECT * INTO CopyOfTable FROM SourceTable WHERE 1=0;

Substitute CopyOfTable and SourceTable with the target and source table names.

What happens under the hood

Executing the SELECT ... INTO statement creates a new table with analogous column definitions as in the source table. However, it doesn't copy any constraints, indexes or triggers that might be associated with the SourceTable. The absence of these components like primary keys, foreign keys and unique constraints in CopyOfTable is noteworthy.

Solution for advanced use-cases

When the source table has an identity column, use the following solution to preserve the exact values in the copied table.

-- Preventing identity crisis in SQL 
SET IDENTITY_INSERT CopyOfTable ON;

INSERT INTO CopyOfTable (Identity, ColumnA, ColumnB) 
SELECT Identity, ColumnA, ColumnB 
FROM SourceTable;

-- We don't need it anymore
SET IDENTITY_INSERT CopyOfTable OFF;

To generate a complete DDL script with constraints and indexes, use SQl Server Management Studio(SSMS). Once you have the DDL script, you can modify the table name and execute to recreate the schema followed by INSERT INTO ... SELECT ... to copy the data.

If you are looking to create multiple clones of a table with different names, you will need to run SELECT * INTO each time with a different new table name.

Proper use of SQL Server tools

Once you have the DDL scripts generated from SSMS along with you, a find and replace routine is what you need to rename tables in the script. Same caution applies here, watch out for conflicts or misplaced references.

Another method is using SQL Server Integration Services(SSIS), a robust tool for data transfer tasks. It provides flexibility to export and import data between tables within the same database.

Data duplication tactics

Whilst duplicating, contemplate on the need for a complete copy, specially for tables with large data. Sometimes, you might want to include only certain columns or only a subset of the data based on specific criteria.

-- Picky SELECT for the picky ones
SELECT Column1, Column2 INTO CopyOfTable FROM SourceTable WHERE SomeCondition;

After copying data, ensure that any unique constraints are not violated due to redundancy. In such cases, you might need to adjust constraints or clean up data.

A visual treat

Creating a new table with an exact replica of data is akin to Xerox photocopying your favourite book:

-- Book (tbl_Original) ==> 🖨️ Photocopier (SELECT) ==> New Book (tbl_Copy)
SELECT * INTO tbl_Copy FROM tbl_Original;

Voila! Now you can lend the original book and still enjoy reading!

Key Point: SELECT * INTO is like magic, creating a carbon copy within the same database(no manual recreation of structure needed).

Managing complex relationships

With a complex relationship structure involving multiple tables, data insertion sequence can be crucial. Make sure the ordering respects the data dependencies. You might need to disable constraints temporarily during the copy process.

-- Because constraints can spoil the fun sometimes
ALTER TABLE CopyOfTable NOCHECK CONSTRAINT ALL;

-- Now we can freely copy the data

ALTER TABLE CopyOfTable CHECK CONSTRAINT ALL;

When the table being duplicated is part of a larger set of related tables, think carefully about how the new duplicate will fit into this ecosystem. Duplicate only what is necessary and keep relational integrity in mind, adapting foreign keys as required.