Exporting Tables From One Database To Another Database

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

    Source

Destination: (Default instance)

  • Server: (local)
  • Database: CSharpCornerDB
  • Table: student

    Default instance

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.

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.

Import and Export Wizard

Step 3: Data Source

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

Data Source

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.

Destination

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.

Table Selection

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:

Edit Mapping

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.

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

progress

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.

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.

student table

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.