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

SQL Server: Efficiently Copying Database Tables Within the Same Database

Learn various T-SQL methods to create a duplicate of an existing table, including its structure, data, or a subset, all within the same SQL Server database.

Copying a database table is a common task in SQL Server, whether for testing, archiving, or creating a new table based on an existing structure. This article explores several T-SQL techniques to achieve this, ranging from simple structure duplication to copying data with or without specific conditions. Understanding these methods will help you choose the most appropriate approach for your specific needs, ensuring data integrity and efficiency.

Method 1: Copying Table Structure and Data

The most straightforward way to copy both the structure and all the data from an existing table to a new one is by using the SELECT INTO statement. This command creates a new table based on the result set of a SELECT query. It's highly efficient for bulk operations and automatically infers column data types and nullability from the source table. However, it does not copy indexes, primary keys, foreign keys, default constraints, or triggers from the source table. These must be added manually after the table creation.

SELECT * 
INTO NewTableName
FROM OriginalTableName;

Copying all columns and data from OriginalTableName to NewTableName.

SELECT Column1, Column2, Column3
INTO NewTableName
FROM OriginalTableName
WHERE Condition = 'Value';

Copying specific columns and filtered data to a new table.

flowchart TD
    A[Start]
    B{Source Table Exists?}
    C[SELECT * INTO NewTable FROM SourceTable]
    D[NewTable Created with Data]
    E[Add Constraints/Indexes Manually]
    F[End]

    A --> B
    B -- Yes --> C
    C --> D
    D --> E
    E --> F
    B -- No --> G[Error: Source Table Not Found]
    G --> F

Flowchart for copying table structure and data using SELECT INTO.

Method 2: Copying Only Table Structure

Sometimes you only need the table structure without any data. This is useful for creating template tables or preparing for data import. You can achieve this using SELECT INTO with a WHERE clause that evaluates to false, or by combining CREATE TABLE with LIKE (SQL Server 2012+), or by scripting the table schema.

SELECT * 
INTO NewTableName
FROM OriginalTableName
WHERE 1 = 0;

Copying only the table structure using SELECT INTO with a false condition.

CREATE TABLE NewTableName LIKE OriginalTableName;

Copying table structure using CREATE TABLE LIKE (SQL Server 2012+).

Method 3: Copying Data to an Existing Table

If you already have an empty table with a compatible structure (or you created one using Method 2), and you want to populate it with data from another table, the INSERT INTO ... SELECT statement is the way to go. This method allows for fine-grained control over which columns are copied and can include filtering conditions.

INSERT INTO ExistingNewTableName (Column1, Column2, Column3)
SELECT Column1, Column2, Column3
FROM OriginalTableName
WHERE Condition = 'Value';

Inserting specific columns and filtered data into an existing table.

INSERT INTO ExistingNewTableName
SELECT * 
FROM OriginalTableName;

Inserting all columns and data into an existing table (columns must match).

sequenceDiagram
    participant User
    participant SQLServer
    User->>SQLServer: Execute SELECT INTO NewTable FROM OldTable
    SQLServer->>SQLServer: Creates NewTable
    SQLServer->>SQLServer: Copies data from OldTable to NewTable
    SQLServer-->>User: NewTable created with data

    User->>SQLServer: Execute CREATE TABLE NewTable LIKE OldTable
    SQLServer->>SQLServer: Creates NewTable with schema only
    SQLServer-->>User: NewTable created (empty)

    User->>SQLServer: Execute INSERT INTO ExistingNewTable SELECT * FROM OldTable
    SQLServer->>SQLServer: Inserts data into ExistingNewTable
    SQLServer-->>User: Data inserted into ExistingNewTable

Sequence of operations for different table copying methods.