Best Ways to Copy Table Records From One Database to Another
This weblog explains 4 methods to copy table records from one database to another. These methods are:
- SQL Query
- Import/Export wizard
- Generate Script
- 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 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. Therefore, for this, you have to generate scripts or use automated SQL Migrator (see in the upcoming sections).
Follow these steps to generate scripts in SQL:
Step-1. Start SQL Server Management Studio (SSMS) in your system.
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 the location to copy the table records data to, Click on “Next” to copy table records from one database to another easily.
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.
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’.
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 the OK button.
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”.
The Final Say
There are not plenty of solutions available that allow users to get the best results. This way, to copy table records from one database to another in SQL server, an automated solution is the best. Therefore, this can be the perfect solution that users need. What makes this utility better is the integration of artificial intelligence & advanced algorithms. Hence, this can be a perfect solution for users’ trouble.
