Copy Data From One Database to Another SQL Server – 4 Methods
If you are looking for the best and fast solution on how to copy data from one database to another SQL server then you are on the right page!
This is a very general question in the SQL community.
Also, this question can be talking about copying data from one database to another on the same server or it can be on a different server.
I will tell you exactly 4 solutions on how to copy your SQL data from source to destination. Keep reading!
4 Methods discussed In this article:
- Professional tool used to copy/move SQL Server Database
- Use SQL Server Management Studio
- Using SQL Queries
- Generating SQL Scripts
1. Tool Preferred by Professionals to Copy Data From One Database to Another SQL Server
SysTools SQL Server Migration Tool is used by professionals to migrate or copy SQL server databases either in the same network or different network.
1. You can easily transfer or make a copy of the following objects:
-Tables, Functions, Views, Stored Procedure, Triggers, etc.
2. You can easily generate SQL scripts.
3. Also, you can choose to export only SQL server database schemas.
4. This tool can scan & restore damaged SQL database as well.
Download the free edition of the tool that exports 25 items and check yourself:
2. Using SQL Server Management Studio
SSMS (in-short) is a software application launched by Microsoft that is used for configuring, managing, and administering all elements within Microsoft SQL Server.
Lets see how you can use SSMS to copy data from database to another in SQL server:
Step-1: Start SQL Server Management Studio.
Step-2: Right-click on the database name >> choose “Tasks” > “Export data…” from the object explorer.
Step-3: The SQL Server Import/Export wizard pops up, click on “Next”.
Step-4: Give authentication details and pick out or choose the source from which you want to copy the data, then, click “Next”.
Step-5: Specify the destination (where to copy the data to) > click on “Next”.
3. Using SQL Query
1. Following command only copies the table schema and data.
Query Syntax: Select * into DestinationDB.dbo.tableName from SourceDB.dbo.SourceTable
If you want to copy constraints, objects, indexes or triggers, then you have to generate Scripts. For this either you can use the above tool or see next method
2. To copy a table in the same database, then you need to first use your database, then use this query:
Query Syntax: select * into newtable from SourceTable
3. To select only a few columns into the destination table.
Query Syntax: select col1, col2 into <destination_table> from <source_table>
4. To Generate Script
By using SSMS and Query, you can only copy the table schema and data but not other objects like views, functions, constraints, triggers and so on. To copy all these things you need to generate SQL scripts.
Let’s see how you can generate a script in SQL:
Step-1: Right-click on the name of the database >> select “Tasks” >> click on “Generate Scripts”.
Step-2: The Script Wizard pops up. Click on “Next”.
Step-3: Choose the Database for which you want to create a script.
Step-4: Next select the object types. Click on “Next”.
Step-5: Select the tables.
Step-6: Choose the Output option for the script.
Step-7: Change the name of the database to the name you want to use this script for.