SQL Server Copy Table From One Database To Another

In this guide, I am going to walk you through the most effective methods to copy tables between databases. I will cover everything from the quick-and-dirty T-SQL commands to the robust GUI tools provided by Microsoft. By the end of this article, you will know exactly which method fits your specific scenario.

SQL Server Copy Table From One Database To Another

Understanding the Landscape: Why Method Matters

Before we write a single line of code, we need to establish context. The method you choose depends entirely on your constraints.

In my experience working with enterprise systems across the US, I usually categorize the requirements into three buckets:

1. The Ad-Hoc Copy: You need the data now for analysis. You don’t care much about indexes or constraints.
2. The Structural Migration: You need an exact replica of the table, including Primary Keys (PKs), Foreign Keys (FKs), and defaults.
3. The Ongoing Sync: You need to move data regularly (we won’t cover complex SSIS or Replication here, but we will touch on scripts that facilitate this).

Let’s dive into the solutions.

Method 1: The Quickest Route (SELECT INTO)

If you need to move a table from Database_A to Database_B and you need it done in seconds, SELECT INTO is the best choice.

How It Works

The SELECT INTO statement creates the destination table on the fly based on the columns returned by the SELECT statement.

The Syntax

You must use the fully qualified names (Database.Schema.Table).

SELECT *
INTO DestinationDB.dbo.NewTable
FROM SourceDB.dbo.OriginalTable;

The Catch (Read This Carefully)

While this is fast, it is not perfect. As an experienced DBA, I have to warn you about what gets left behind. When you use SELECT INTO, SQL Server copies the column names and data types, but it does not copy:

• Indexes
• Constraints (Primary Keys, Foreign Keys)
• Triggers

It effectively gives you a heap (a table without a clustered index). If you are building an application on top of this new table, you will need to recreate those objects manually.

Example

SELECT *
INTO AzureLessons.dbo.SalesOrder
FROM Test.dbo.Order;

Method 2: The Controlled Approach (INSERT INTO... SELECT)

Suppose you already have a table structure defined in your target database. Perhaps your DevOps team already deployed the empty schema to the QA environment, and now it is your job to populate it with data.

In this case, SELECT INTO won’t work because the table already exists. Instead, we use INSERT INTO... SELECT.

Why I Prefer This for Production

I prefer this method for formal migrations because it allows for column mapping. You don’t have to copy every column. If the source table has a SocialSecurityNumber column that you don’t want in your test environment, you simply omit it from the column list.

The Syntax

INSERT INTO DestinationDB.dbo.TargetTable (Column1, Column2, Column3)
SELECT Column1, Column2, Column3
FROM SourceDB.dbo.SourceTable
WHERE Region = 'East_Coast';

Handling Identity Columns

This is the most common error. If your target table has an Identity column (an auto-incrementing ID), SQL Server will block you from inserting your own values into it.

To bypass this, you must toggle the IDENTITY_INSERT setting.

SQL

SET IDENTITY_INSERT DestinationDB.dbo.TargetTable ON;

-- Run your INSERT statement here

SET IDENTITY_INSERT DestinationDB.dbo.TargetTable OFF;

Method 3: The GUI Approach (Import/Export Wizard)

Not everyone loves writing T-SQL queries, and honestly, for very large datasets or complex mappings, I sometimes prefer the GUI myself. The SQL Server Import and Export Wizard is a fantastic tool built right into SSMS.

I often use this when I am moving data between different types of sources, but it works perfectly for SQL-to-SQL transfers.

Steps

1. Launch the Wizard: Right-click on your source database in SSMS, go to Tasks, and select Export Data. Check out the screenshot below for your reference.
2. Choose Source and Destination: You will likely select “SQL Server Native Client” for both. Ensure you point the destination to the correct server (e.g., your generic US-East-01 server).
3. Select Tables: You can check a box next to the tables you want to copy. You can even copy multiple tables at once—something the T-SQL methods above make difficult.
4. Edit Mappings: This is the killer feature. You can click “Edit Mappings” to change data types on the fly or tell the wizard to create the destination table if it doesn’t exist.

Check out the screenshots below for the complete steps.

Pro Tip: At the end of the wizard, you have the option to “Save as SSIS Package.” If this is a task you need to repeat every month for your finance department, save the package. You can then automate it to run via SQL Agent.

Method 4: Generating Scripts (Schema + Data)

This is a method I use heavily when sending data to a client or a vendor who doesn’t have direct access to our network. If I need to send a Products table to a consultant, I can’t just give them a database connection.

I generate a .sql script that contains both the table creation logic and the data itself.

How to do it:

1. Right-click the database in Object Explorer.
2. Select Tasks > Generate Scripts.
3. Select the specific table.
4. Crucial Step: Click “Advanced” in the “Set Scripting Options” window.
5. Look for the setting “Types of data to script”.
6. Change it from “Schema Only” to “Schema and Data”.

When you run the resulting script on the destination server, it creates the table and runs a massive list of INSERT statements to populate it.

Warning: I only recommend this for smaller tables (under 100,000 rows). If you try to script out a table with 10 million rows, your text editor (and likely SSMS) will crash trying to open the file.

Comparison: Which Method Should You Use?

Conclusion

Copying a table in SQL Server is a fundamental skill that bridges the gap between development, testing, and production. Whether you are using the lightning-fast SELECT INTO for a quick sandbox test or the meticulous Generate Scripts method for a cross-country data transfer, understanding the mechanics behind these tools is essential.

Always verify your row counts after a copy. A simple SELECT COUNT(*) on both source and destination can save you hours of headache down the road.

You may also like the following articles:

• SQL Server Copy Table To Another Table
• SQL Server In Memory Tables
• SQL Server Insert Into Select Statement
• SQL Server Import Excel File Into Temp Table
• Describe Table in SQL Server
• Rename Table In SQL Server

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.