Exporting Tables From One Database To Another Database

Abhishek Yadav

SQL Serverexport database one to another in SQL serverExporting Table

7 Comments

Join the conversation! Your thoughts help the community grow.

Santhakumar Munuswamy10y ago
Thanks for nice article.
Humayun Kabir Mamun10y ago
Nice...
Aasim Abdullah10y ago
A good article. Unfortunately, Export wizard is less commonly used on production servers. Most of the time when both databases (on same or separate instances) are accessible through Linked Server Connection, then four part object naming is used to copy data. (Insert into dbo.student SELECT * FROM ABHI2012.University.dbo.student)
- Abhishek Yadav10y ago
  Agreed Aasim Abdullah, Also if we want to perform Import Export once or twice in production environment, we can use export wizard. If it's a repetitive task, we can go with T-SQL script or SSIS packages. Thanks for your view... :)
Arul R10y ago
Nice share
Debasis Saha10y ago
Good One..
Yashwant Vishwakarma10y ago
Awesome one, thanks for sharing !!

In my previous article we learned about jobs, and we also created types like T-SQL job, job from a maintenance plan, etc.

In this article, we’ll perform this operation with the help of “SQL Server Import and Export Wizard," then, in our next article, we’ll see the BCP command to export and import any table from one database to another.

To perform this operation, we have the following source and destination database.

Source: (Named instance)

Server: (local)\ABHI_SQL2012
Database: University
Table: student

Destination: (Default instance)

Server: (local)
Database: CSharpCornerDB
Table: student

As you can see, we don’t have any student table in our CSharpCornerDB database on local server.

And now, we’ll export the data of the student table from the named instance to the database on a local database i.e. on CSharpCornerDB.

Let’s begin with Export and Import wizard.

The following is our data in Named Instance student table.

Step 1: Beginning

Right click on Database, select Tasks and then select select Export Data.

Select Export Data

Step 2: Welcome Window

You’ll get the following “Import and Export Wizard” as shown below. Click Next.

Step 3: Data Source

At this step, select your source data and database name. Click Next.

Step 4: Destination.

Select Destination, where you want to export your data. Here I’m selecting Server name as ‘(local)’ and I want to transfer my source table in CSharpCornerDB database as I selected below. Click Next.

Step 5: Operation Mode

Here either you can select existing tables from source database or you can write your own query to manipulate your data. As we’re exporting entire table, we’ll select the first option.

If you want to select some of the columns or some other kind of manipulation, you can go with option 2. Click Next.

Operation Mode

Step 6: Table Selection

In this window, select your desired tables which you want to import to your destination database.

After selecting your tables, you’ll see 2 options below ListView.

Edit Mapping
Preview

By clicking on Preview option, you can see your selected data from table.

Preview option

If you want to modify or alter your destination table structure, you can perform it by selecting the ‘Edit Mapping’ option which looks like the following window:

This window will allow you to changethe destination column name, column size, etc. Click OK and you’ll get your desired structure. Click Next.

Step 7: Save or Run

In this window, either you can run your operation immediately or you can save it as package. Saving package option will add another step where it’ll ask you to enter package details. We’re skipping this package part. Click Next.

Step 8: Summary.

This step will give you a summary of the operation you’re going to perform, as you can see in the following image. It is showing the source, destination server and also specifying other details which anyone can easily understand. Click Next.

Summery

Step 9: Execution

At this step, your operation starts and you’ll get the progress indicators.

progress indicators

Once it completes the operation, you’ll get a message that how many rows are transferred in the newly created table.

Now, at this point, we’ve successfully exported data from one server to another server. Let’s check our data on local server, i.e., our Destination Server.

So, here I connected my local server and now we can easily see student table in CSharpCornerDB database and below is the content for the same.

So, with this, we’ve successfully exported our table data from one server to another server with help of wizard.

In our next article, we’ll see how we can use bulk copy command to export and import data from one server to another, till then keep learning and sharing.