Duplicating a TABLE using Microsoft SQL Server Management

sql

prompt-engineering

best-practices

data-integrity

byAlex Kataev·Jan 2, 2025

For a copy-pastable quick fix, here are two SQL scripts that copy a table. Use SELECT INTO to duplicate a table and its data:

SELECT * INTO NewTable FROM ExistingTable;

To copy only the structure—no data—add a false condition:

SELECT * INTO NewTable FROM ExistingTable WHERE 0 = 1;

Take a sip of coffee ☕ and note: Indexes, constraints, and triggers aren't copied. You have to manually script the original table, then run that script for the new table.

Beyond Basic: Advanced Table Duplication Techniques

Scripting Table Structure plus Indexes and Constraints

Using SELECT INTO is quick and efficient, but it skips indexes and constraints. Manually get those by scripting out the original table:

Right-click on the table in your SSMS.
Go to Script Table as > CREATE To > New Query Editor Window.
Change the table name in the script to your new table name, then hit F5.

Keep Identity Columns Functional

Tables with identity columns need a bit more care to keep the sequence:

-- It's like enabling the superpower mode
SET IDENTITY_INSERT NewTable ON;

-- Star Trek fans, it's replication time!
INSERT INTO NewTable SELECT * FROM ExistingTable;

-- Don't forget to shut off the superpower mode
SET IDENTITY_INSERT NewTable OFF;

Alongside identity columns, remember to deal with foreign key relationships. Script them out and then reapply to the new table.

Handling Big Data Tables

Working with large tables could be like trying to eat an elephant in one bite. To prevent choking (locking and resource congestion), consider dividing the task into smaller bites—batch the data transfer:

-- It's a loop, not a roller coaster ride, I promise
WHILE (SELECT COUNT(*) FROM ExistingTable) > 0
BEGIN
    -- It's like having a tray that fits only 1000 items. You can change the tray size though
    INSERT INTO NewTable SELECT TOP (1000) * FROM ExistingTable;
    
    -- You need to progressively exclude/ignore transferred data. Don't repeat yourself, it's not Groundhog Day yet
END

Heavy Lifting: Mastering Advanced Table Duplication Tools

For replicating tables accurately with every nook and cranny intact, diving into more systematic approaches is advised:

SQL Server Import and Export Wizard

Who said wizards existed only in fantasy? SQL Server Management Studio provides one:

Right-click on the database.
Select Tasks > Export Data....
Follow the on-screen wizard. He's friendly, he will guide you step-by-step.

Use Script Generation Feature of SSMS

Rather not wear the wizard hat? SSMS can help generate scripts that cover all table aspects:

Navigate to the Object Explorer.
Right-click the database, then Tasks > Generate Scripts.
Choose Schema and Data for a full-table script.

Quick Duplication, Just the Structure

Need just a copy of the table structure without data via the SSMS interface? Here's how:

In Object Explorer, a right-click on the table is your first step.
Script Table as > CREATE To > New Query Editor Window.
Switch the original table name with the name you have chosen for your new table.

Prep for Potential Pitfalls

Big Data Handling

Copying large data might throw you a timeout issue party or a transaction log overflow event. Batch insertion can shush these problems.

Server Performance

Large table duplication can be like a demanding teenager, resource-intensive. Be wise and pick off-peak hours, or opt for performance monitoring tools to strategize the duplication.