Home » SQL Server » Best Ways to Copy Table Records From One Database to Another

Best Ways to Copy Table Records From One Database to Another

Samuel Adams | Modified: 2021-12-14T14:29:42+00:00|SQL Server | 4 Minutes Reading

This weblog explains 4 methods to copy table records from one database to another. These methods are:

  • SQL Query
  • Import/Export wizard
  • Generate Script
  • SysTools SQL Server Migration Tool

Kindly note that  using an automated tool, you can export table records from one database to another of same server or different server. You can also export SQL server files (offline) to a online server.

Using SQL Queries to Copy Table Records From One Database to Another

The syntax of the query is like this:
Select * into DestinationDB.dbo.tableName from SourceDB.dbo.SourceTable

But you have to note, using this query won’t let you copy objects, indexes, triggers or constraints. For this you have to generate scripts or use SysTools automated SQL Migrator (see in the later sections).

You can also select few columns into the destination table, here is the syntax for it:
select col1, col2 into <destination_table> from <source_table>

Using SQL Server Import/Export wizard

In this method, we will Import/Export wizard of SQL Server management Studio.

But again you CANNOT copy objects, indexes, triggers or constraints. For this you have to generate scripts or use SysTools automated SQL Migrator (see in the upcoming sections).

Follow these steps to generate scripts in SQL:

Step-1: Start SQL Server Management Studio.

Step-2: Right-click on the database whose table records you want to copy, then click on “Tasks” >> “Export data…” in the Object Explorer.

Step-3: The Import/Export wizard will appear, click on “Next”.

Step-4: Provide logins and authentication and click the source from which you want to copy the table records, click “Next”.

Step-5: Specify location to copy the table records data to, click on “Next”.

Generate Script to Copy Table Data

Though it’s a long process, but the advantage of using this method is that you can copy views, functions, constraints, triggers and so on by generating scripts.

Perform the following actions to generate a script:

Step-1: Right-click on the database name, then click on “Tasks” >> “Generate Scripts”. You can see the Script Wizard has open. Click on “Next”.

Step-2: Now, click on the Database that you want to convert to script.

Step-3: Select the ‘Object types’. Click on “Next”. Select the tables.

Step-4: Choose the Output option for the script.

Step-5: Change the database name to the name you want to run this script for.

Copy Table Records From One Database to Another Using Automated Tool

All the above methods were time consuming and some of them are not efficient. Hence, you should go for an automated software that has been specially designed for migrating SQL data to anywhere. This tool is the best software to export SQL database to another server or the same server.

For Windows

Download Now Purchase Now

There are many advantages of using this tool:

– You can transfer/copy SQL server data from a live server or Offline file.
– You can export SQL server database to a live SQL server or convert them to compatible scripts.
– Provides option to export selective data and SQL objects (Tables, Stored Procedures, Functions, Views, etc.)
– You can select option to whether to export schema with data or without data.
– Can save data in an existing database or create a new database.

Now, let us see how to copy table records from one database to another using this tool:

Step-1: You can download and install the tool on your Windows OS and click on ‘Open’.

SQL Server Migration Tool

Step-2: Choose between Online/Offline mode and provide the login details and authentication. Also select the database from which you want to copy the table. Click OK.

Step-3: The tool will run a thorough scan of the database and will get preview of the data. Click on Export.

Step-4: Here you to provide destination details. There are various option that you can choose for the way in which you want to copy the data. Click on “Export”.

copy table records from one database to another

email-backup-banner