SQL Server Copy Table To Another Table

by

In this article, I am going to walk you through the exact methods I use to copy tables in SQL Server, from the quick-and-dirty one-liners to the robust enterprise-grade scripts.

SQL Server Copy Table To Another Table

Method 1: The “Select Into”

When I need to make a quick backup of a table before running a massive UPDATE or DELETE statement, this is my go-to.

The SELECT INTO statement is powerful because it creates the destination table for you on the fly based on the columns in your SELECT statement.

Why I Love It

  • Speed: It is minimally logged (if your recovery model is set to Simple or Bulk-Logged), meaning it screams fast compared to a standard insert.
  • Simplicity: You don’t need to write a CREATE TABLE script first.

The Syntax

You simply select the data you want and tell SQL Server where to put it.

SQL

SELECT *
INTO dbo.Employees_ArchiveN
FROM dbo.Employees_Main;

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server Copy Table To Another Table
SQL Copy Table To Another Table

The “Gotcha”

This is the part that trips up 90% of the developers I interview. SELECT INTO does not copy your objects.

It will copy the column names and data types, but it leaves behind:

  • Primary Keys
  • Foreign Keys
  • Indexes
  • Triggers
  • Constraints

If you are parking data for an hour, this is fine. If you are building a permanent table, you will need to do some cleanup afterward.

Method 2: The “Insert Into Select” (The Append)

Scenario: You already have a destination table created (perhaps with specific data types or constraints), and you just want to move data into it.

In this case, SELECT INTO won’t work because the table already exists. You need the INSERT INTO... SELECT method.

When to Use This

  • Appending Data: You are archiving last year’s sales data into a Sales_Archive table.
  • Schema Control: You want the destination table to have different data types than the source (e.g., converting a VARCHAR to an INT during the copy).

The Syntax

SQL

INSERT INTO dbo.Employees_ArchiveN (EmployeeID, FirstName, LastName, Department, HireDate)
SELECT EmployeeID, FirstName, LastName, Department, HireDate
FROM dbo.Employees_Main
WHERE HireDate > '2023-01-15';

After executing the above query, I got the expected output as shown in the screenshot below.

how to copy table to another table in sql

Pro Tip: The Identity Trap

If your destination table has an IDENTITY column (an auto-incrementing ID), SQL Server will scream at you if you try to insert your own values into it.

To bypass this, you must turn on the “Identity Insert” switch before you run your query else you will get Cannot insert explicit value for identity column in table error:

SQL

SET IDENTITY_INSERT dbo.Sales_Archive ON;

-- Run your Insert statement here

SET IDENTITY_INSERT dbo.Sales_Archive OFF;

Method 3: Copying Schema Only

Sometimes, I don’t want the data. I just want the structure. Maybe I’m creating a new table for a new fiscal year in a database for a client.

You can use a “hack” with SELECT INTO by adding a condition that is never true.

The Zero-Row Trick

SQL

SELECT *
INTO dbo.NewTable_Structure
FROM dbo.OriginalTable
WHERE 1 = 0;

After executing the above query, I got the expected output as shown in the screenshot below.

copy table to another table sql

Since “1” never equals “0”, SQL Server creates the table structure to hold the results, but copies zero rows. It’s a brilliant little shortcut for cloning table definitions.

Comparison: Select Into vs. Insert Into

Here is a quick breakdown of when I use which method.

FeatureSELECT INTOINSERT INTO … SELECT
Table Exists?No (Creates it)Yes (Must exist)
SpeedFaster (Minimal Logging)Slower (Full Logging)
Copies Indexes?NoNo (Preserves existing ones)
Use CaseQuick Backups / StagingMerging / Archiving Data

Advanced: Copying Across Database

It is common to move data between a Production database and a Reporting database.

You don’t need fancy ETL tools for this if they are on the same server. You just need to use the Three-Part Name syntax (Database.Schema.Table).

The Cross-Database Script

SQL

SELECT *
INTO ReportingDB.dbo.Customers_Snapshot
FROM ProductionDB.dbo.Customers;

Note: The user running this query needs permission on both databases. If you get a “Permission Denied” error, talk to your DBA (or if you are the DBA, check your user mapping).

Handling Large Tables

If you try to copy a table with 50 million rows using a single INSERT statement, you are going to crash your transaction log. I have seen servers in data centers freeze up because a developer tried to move 100GB of data in one transaction.

For massive tables, you must Batch your copy.

The Batching Strategy

Instead of doing it all at once, you do it in chunks. While I won’t write the full loop code here, the logic is:

  1. Copy the first 10,000 rows.
  2. Checkpoint (commit).
  3. Copy the next 10,000 rows.
  4. Repeat.

This clears the transaction log periodically, keeping your server healthy and your DBA happy.

Copying Indexes and Constraints

As mentioned earlier, SELECT INTO is lazy. It drops your indexes. If you need a perfect clone of a table—including keys, indexes, and defaults—you cannot do it with a simple T-SQL query.

The SSMS Script Generation Method

This is the most reliable way to get a perfect copy.

  1. Open SQL Server Management Studio (SSMS).
  2. Right-click the table you want to copy.
  3. Select Script Table as > CREATE To > New Query Editor Window.
  4. Change the table name in the script (e.g., from Orders to Orders_Copy).
  5. Run the script to create the empty structure with all constraints.
  6. Use INSERT INTO... SELECT to populate the data.

Conclusion

Copying a table in SQL Server is a fundamental skill, but “how” you do it depends entirely on “why” you are doing it.

  • Need a 5-second backup? Use SELECT INTO.
  • Need to merge data into an archive? Use INSERT INTO.
  • Need a perfect clone with indexes? Use the SSMS Script Generator.

Knowing these distinctions prevents performance bottlenecks and ensures data integrity. So the next time you need to duplicate data, you won’t just be copying; you’ll be architecting.

You may also like the following articles: