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
  
  

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.



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.



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.



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.



Step 9: Execution

At this step, your operation starts and you’ll get the 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.