Information2 use cookies to help you have a superior and more admissible browsing experience on our website. Privacy Policy
Loading...
By:
Copying a table from one database to another is a common task for SQL Server administrators, developers, and database engineers. Whether you need to migrate data, create a testing environment, duplicate production data, or move tables between different servers, SQL Server provides several ways to complete the task.
The best method depends on your requirements:
In this guide, we will explain 5 practical methods to copy a table from one SQL database to another.
The SELECT INTO statement is one of the easiest ways to copy tables from one SQL database to another. It allows you to create a new table in the destination database and copy data from an existing table in the source database with a single SQL query.
This method is commonly used when:
However, SELECT INTO has some limitations. It copies columns and data types but does not automatically copy indexes, constraints, triggers, or permissions.
Step 1. Open SQL Server Management Studio and Connect to Your Database.
Open SQL Server Management Studio and connect to the SQL Server instance that contains the source database.
After connecting:
Step 2. Execute the SELECT INTO Statement.
Use the following query format:
SELECT *
INTO DestinationDatabase.dbo.NewTableName
FROM SourceDatabase.dbo.SourceTableName;
For example:
SELECT *
INTO SalesArchive.dbo.CustomerBackup
FROM SalesDB.dbo.Customer;
Customer table in the SalesDB database.CustomerBackup table in the SalesArchive database.After running the query, SQL Server will automatically create the destination table.
Step 3. Verify the Copied Table
After the query completes successfully:
Example:
SELECT *
FROM SalesArchive.dbo.CustomerBackup;
You can compare the number of records between the source and destination tables:
SELECT COUNT(*)
FROM SalesDB.dbo.Customer;
SELECT COUNT(*)
FROM SalesArchive.dbo.CustomerBackup;Advantages
Limitations
If you need to copy a table with its complete database objects, consider using SQL Server Management Studio or scripting methods.
Another common method to copy a SQL table from one database to another is using the INSERT INTO SELECT statement.
Unlike SELECT INTO, this method requires the destination table to already exist. Instead of creating a new table, SQL Server inserts data into an existing table structure.
This approach is useful when:
Step 1. Create the Destination Table.
Before using INSERT INTO SELECT, create the target table in the destination database.
For example:
CREATE TABLE ArchiveDB.dbo.EmployeeBackup
(
EmployeeID INT,
EmployeeName VARCHAR(100),
Department VARCHAR(50)
);
The destination table structure should match the source table columns you plan to copy.
Step 2. Run the INSERT INTO SELECT Query.
The basic syntax is:
INSERT INTO DestinationDatabase.dbo.TargetTable
SELECT *
FROM SourceDatabase.dbo.SourceTable;
Example:
INSERT INTO ArchiveDB.dbo.EmployeeBackup
SELECT *
FROM HRDatabase.dbo.Employee;
This copies all rows from the Employee table in HRDatabase into the EmployeeBackup table in ArchiveDB.
Step 3. Copy Selected Columns or Filter Specific Data.
One advantage of INSERT INTO SELECT is that you can control what data is copied.
For example, copy only selected columns:
INSERT INTO ArchiveDB.dbo.EmployeeBackup
(
EmployeeID,
EmployeeName
)
SELECT
EmployeeID,
EmployeeName
FROM HRDatabase.dbo.Employee;
You can also copy only specific records by adding a WHERE condition:
INSERT INTO ArchiveDB.dbo.EmployeeBackup
SELECT *
FROM HRDatabase.dbo.Employee
WHERE Department = 'IT';
Step 4. Verify the Copied Data.
After execution, verify that the destination table contains the expected data:
SELECT *
FROM ArchiveDB.dbo.EmployeeBackup;
You can also compare record counts:
SELECT COUNT(*)
FROM HRDatabase.dbo.Employee;
SELECT COUNT(*)
FROM ArchiveDB.dbo.EmployeeBackup;| Feature | SELECT INTO | INSERT INTO SELECT |
|---|---|---|
| Creates a new table automatically | Yes | No |
| Requires destination table | No | Yes |
| Copies table data | Yes | Yes |
| Supports selecting specific columns | Limited | Yes |
| Supports filtering records | Limited | Yes |
| Copies indexes and constraints | No | No |
| Best for | Quick table copy | Controlled data transfer |
For simple one-time table duplication, SELECT INTO is usually faster. If you need more control over the copied data, INSERT INTO SELECT is a better option.
SQL Server Management Studio (SSMS) provides a graphical way to copy tables between databases without manually writing SQL statements. Compared with SELECT INTO and INSERT INTO SELECT, SSMS allows you to generate scripts that include more database objects, such as table structure, indexes, constraints, and data.
This method is suitable when you need to:
In SSMS, the most common way to copy a table is by using the Generate Scripts Wizard.
Step 1. Open Generate Scripts Wizard in SSMS.
First, open SQL Server Management Studio and connect to your SQL Server instance.
Follow these steps:
Step 2. Select the Table to Copy.
The Generate Scripts Wizard will guide you through the scripting process.
In the wizard:
For example, if you want to copy the Customer table:
Source Database
└── Tables
└── dbo.Customer
will be selected.
Step 3. Configure Script Options.
In the Script Options page, you can decide what SQL Server should include in the generated script.
Important settings include:
Types of data to script
Set:
Types of data to script:
Schema and Data
This ensures that SSMS generates both:
Other options include:
For a complete table copy, choose Schema and Data.
Advanced Options
You can also configure additional settings:
These options help preserve the original table design.
Step 4. Generate the SQL Script.
After selecting the required options:
SSMS will generate a SQL script similar to:
CREATE TABLE [dbo].[Customer]
(
[CustomerID] INT,
[CustomerName] VARCHAR(100)
);
INSERT INTO [dbo].[Customer]
VALUES
(1, 'John Smith');
This script can then be executed in another database.
Step 5. Execute the Script in the Destination Database.
To copy the table:
After execution, SQL Server will create the table and insert the data.
Advantages
Limitations
For occasional table copying, SSMS is a convenient solution. However, enterprises that need automated synchronization between databases may require a dedicated replication solution.
Copying a SQL table between different servers is more challenging than copying tables within the same SQL Server instance. When the source and destination databases are located on separate servers, you need to establish communication between the servers or use a data transfer tool.
This scenario is common when organizations need to:
There are two common approaches: Using SQL Server Linked Server with queries and Using SQL Server Import and Export Wizard.
A Linked Server allows one SQL Server instance to access data from another SQL Server instance. After configuring a Linked Server connection, you can use SQL queries to copy data directly between databases.
Step 1. Configure a Linked Server Connection.
In SSMS:
Configure:
Example:
Linked Server Name:
SourceSQLServer
Step 2. Test the Linked Server Connection.
Before copying data, verify that the connection works.
Run:
EXEC sp_testlinkedserver
'SourceSQLServer';
Step 3. Copy Table Data Using a Cross-Server Query.
After creating the Linked Server, use a four-part object name:
LinkedServer.Database.Schema.Table
Example:
INSERT INTO DestinationDB.dbo.Customer
SELECT *
FROM SourceSQLServer.SourceDB.dbo.Customer;
This query copies data:
Source SQL Server
|
|
Linked Server
|
|
Destination SQL Server
SQL Server Import and Export Wizard is another built-in method for moving tables between different servers.
It is suitable when:
Step 1. Launch Import and Export Wizard.
In SSMS:
Tasks → Export Data
Step 2. Select Source and Destination Servers.
Configure:
Data Source
Select:
Destination
Select:
Step 3. Select Tables to Transfer.
Choose:
Copy data from one or more tables
Then select the tables you want to copy.
Source:
SalesDB.dbo.OrderDetails
Destination:
ArchiveDB.dbo.OrderDetails
Step 4. Complete the Data Transfer.
Review the configuration:
After completion, verify the copied table in the destination database.
| Method | Best For | Requires Target Table | Supports Different Servers | Copies Schema |
|---|---|---|---|---|
| SELECT INTO | Quick table copy | No | Limited | Basic |
| INSERT INTO SELECT | Controlled data transfer | Yes | Limited | No |
| SSMS Generate Scripts | Copy schema and data | No | Yes | Yes |
| Linked Server Query | Cross-server copying | Yes | Yes | No |
| Import/Export Wizard | GUI-based migration | No | Yes | Yes |
For simple one-time table copying:
For copying tables with database objects:
For copying tables between different SQL Server instances:
However, these methods are mainly designed for manual or one-time transfers. If you need continuous synchronization, real-time replication, or automated database migration, an enterprise data replication solution is more suitable.
The methods above are suitable for one-time SQL table copying. However, enterprises often need continuous data synchronization, low-downtime database migration, or replication between different database platforms.
i2Stream is a real-time database replication and synchronization software that helps organizations automatically replicate data changes between heterogeneous database environments. It captures database changes and continuously delivers them to target systems, reducing the need for manual data copying.
For occasional table copies, SQL queries and SSMS are usually enough. For enterprise scenarios that require automated synchronization or continuous replication, i2Stream provides a more efficient solution.
Although SQL Server provides multiple ways to copy tables, database administrators may encounter several challenges during the process.
Understanding these issues helps you choose the right method and avoid unexpected problems.
A common issue with methods such as SELECT INTO is that only columns and data are copied.
Objects that may not be transferred include:
For example, after copying a table using:
SELECT *
INTO NewDatabase.dbo.Customer
FROM OldDatabase.dbo.Customer;
the new table may contain the data but lack the original performance optimizations and relationships.
For a complete copy, consider using:
When copying millions or billions of rows, simple SQL statements may impact database performance.
Potential problems include:
For large-scale data movement, consider:
When copying tables between different SQL Server instances, permission issues are common.
Typical problems include:
Before performing a cross-server copy, verify:
SQL table copy methods are effective when you need to move data once.
However, they are not designed for scenarios where data changes continuously.
For example:
A company migrating from SQL Server to PostgreSQL may need to keep both databases synchronized during the migration period.
Running manual copy commands repeatedly can be:
For continuous data synchronization, a real-time replication solution is usually a better approach.
1. How do I copy a table from one database to another in SQL Server?
You can copy a table from one SQL Server database to another using several methods, including SELECT INTO, INSERT INTO SELECT, SQL Server Management Studio (SSMS), and Import and Export Wizard.
For a quick copy:
SELECT *
INTO TargetDatabase.dbo.TableName
FROM SourceDatabase.dbo.TableName;
For more control over copied columns and data:
INSERT INTO TargetDatabase.dbo.TableName
SELECT *
FROM SourceDatabase.dbo.TableName;
2. How do I copy tables from one SQL database to another different server?
To copy tables between different SQL Server instances, you can use:
Linked Server allows you to execute cross-server SQL queries, while Import and Export Wizard provides a graphical transfer process.
3. Can SELECT INTO copy indexes and constraints?
No. SELECT INTO copies:
It does not copy:
If you need to preserve these objects, use SSMS Generate Scripts or another migration approach.
4. How do I copy a table using SQL Server Management Studio?
In SSMS, you can copy a table by:
This method copies both the table structure and data.
5. What is the best way to copy SQL tables continuously?
For continuous table synchronization, manual SQL scripts are not ideal because they only perform one-time transfers.
A CDC-based replication solution such as i2Stream can automatically capture database changes and replicate them to target systems in real time.
Copying a table from one SQL Server database to another can be done using different methods depending on your needs. For simple one-time transfers, SQL queries and SSMS provide convenient solutions.
For enterprises that require continuous synchronization, low-downtime migration, or cross-platform database replication, i2Stream provides an automated and reliable way to keep data synchronized across different database environments.
