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

In SQL Server, there are several ways to create a copy of a table within the same database. Whether you want to duplicate just the structure or both structure and data, SQL Server provides simple methods to achieve this.

Here are the most common approaches:

Copy both structure and data using SELECT INTO

 SELECT * INTO NewTable
FROM ExistingTable;

• This creates a new table (NewTable) with the same columns and copies all the data from ExistingTable.
  
• Keep in mind that indexes, keys, and constraints are not copied.
  

Copy only structure without data

 SELECT * INTO NewTable
FROM ExistingTable
WHERE 1 = 0;

This creates an empty copy of the table with the same structure.

Copy data into an already created table using INSERT INTO

 INSERT INTO NewTable
SELECT * FROM ExistingTable;

Useful if you’ve already created the table manually (with indexes or constraints).

Copy structure including indexes and constraints

• You’ll need to use Script Table as → CREATE in SQL Server Management Studio (SSMS) to generate the table definition.
  
• After creating the new table, use INSERT INTO to copy the data.
  

 Key takeaways:

• Use SELECT INTO for quick duplication when constraints aren’t needed.
  
• Use SSMS scripting if you need indexes, keys, or triggers.
  
• Always rename the new table properly to avoid conflicts.
  
• This way, you can easily duplicate a table in SQL Server for testing, backup, or analysis without affecting the original data.