How to copy data between databases in SQL Server?

Syntax

To copy data from one database to another database in SQL Server, here is the generic syntax:

SELECT * 
INTO destination_db.schema.table
FROM source_db.schema.source_table;

When executing this SQL statement, SQL Server will create a new table in the destination schema, and copy all table structure and table data from the source table.

You can choose to copy some specific columns:

SELECT column1, column2 
INTO destination_db.schema.table
FROM source_db.schema.source_table;

It can be used to copy data to a new table within the same database:

SELECT * 
INTO new_table 
FROM source_table;

Note that it doesn’t copy other database objects such as trigger or indexes.

Example

To copy table employees in schema dbo in database company, to the table employees in schema dbo, in database salary:

SELECT * 
INTO salary.dbo.employees
FROM company.dbo.employees;

In TablePlus, you can use the Import & Export feature to move data from one database to another database. Here is the step-by-step guide:

To export:

When connected to the source database, select the tables you want to copy data from.
Navigate to menu File > Export… or right click on the selection and choose Export…
Use the tab SQL to export data as a SQL Dump file
Save the exported file onto your hard drive.

Export

To import:

Open the destination database
Navigate to menu File > Import > From SQL Dump…
Select the exported file and import

Import

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.