How to Copy Data from One Database to Another in SQL Server

Short Introduction!
     Microsoft SQL Server (SQL) is a relational database management system developed which is developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network.

Sometimes, we need to copy or move database/data from one database to another database. But mostly of persons don't know how to do this handy trick. This is not a trick it is functionality which is provided by defualt from Microsoft. In this Tutorial, I will tell you that how to do this task by using following two methods.

1. Copy or Move Data Using a SQL Query.
2. Copy or Move Data Without Using SQL Query.

Here I have created two databases named "test and testExample". In database "test", I have a table named DataP containing data like ID, Name, Address and so on while in another database "testExample", I have table name UserData. Here I want to copy the table DataP to other database name testExample and in table UserData. We can do this using various methods.

Now I am going to copy this data by using both methods. Both Methods are captioned below;

Method - 1:

Using SQL QUERY

   To copy data from dbo.test into dbo.testExample the requisite query syntax is here:

Select * into DestinationDB.dbo.tableName from SourceDB.dbo.SourceTable

In my case my query for copy data from my databases is as follow;

Example:

select * into testExample.dbo.UserData from test.dbo.DataP

This command only copies the table schema and data. If we want to copy objects, indexes, triggers or constraints, then we need to generate Scripts Which is another method that will talk about later in this article.

---

   If we want to copy a table in the same database, then we need to first use our database, then query syntax is as follow:

Query Syntax

use databasename select * into newtable from SourceTable;

In my case my query for copy data from my databases is as follow;

Example:

use testExample select * into UserData from UserRegistrationData

   Here i copied data from table named "UserRegistrationData" into "UserData" and these both tables are in same database name "testExample".

---

   Here We can also select only a few columns into the destination table.

Query Syntax

select col1, col2 into "destination_table" from "source_table";

In my case my query for copy data from my databases is as follow;

Example:

select Id,Name into testExample.dbo.UserData from test.dbo.DataP

Here i copied only two columns which are id and Name to UserData table in testExample from DataP table in Test database.

---

Method - 2:

Copy Data without Using SQL QUERY

To copy data from one database to antoher database (in my case from dbo.test into dbo.testExample) follow these steps;

1. Open SQL Server Management Studio.
2. Right-click on the database name (In my Case at "test.dbo") then select "Tasks" click on "Export data".
   See the picture [1] below;

3. The SQL Server Import/Export wizard opens; click on "Next".
4. Provide authentication (user name and password) and select the source from which you want to copy the data; click "Next".
   See the picture [2] below;

5. Again Provide Server Name, data source and database name to Specify where to copy the data and click on "Next".
   See the picture [3] below;
6. Now Select on anyone option from windows appear and Specify the method. and Click Next.
   See the picture [4] below;

7. Now Select Source Table and Views from window and then again click to Next.

After that copy data process will begin it take sometime according to size of data abd after process will get done you see message like The Execution is done Successfull. That's it. We have done our task.

if you have still have any confusion regarding "how to copy Data from SQL then simply click on this to see the demo. and you can also contact usor leave a comment below.