The Database Design Resource Center

Import and export in SQL Server

The Advantage of the Import and Export in SQL Server Tools

While SQL Server can be used to house the data that your company maintains on a daily basis and offers a way to effectively and efficiently manage the objects and data within that database, there are often times when getting initial data into a database can be a hassle.

However, not only does SQL Server manage the data and objects contained within the database, it has tools that can be used to get data into tables quickly from a variety of external sources.

SQL Server provides to those who use it, the ability to import and export in SQL Server to many different formats. This can be extremely useful for someone wanting to quickly get data into the database or out of the database for any reason.

For example, an agency that sells mailing lists could easily export the data from a SQL Server database to a format that could be burned onto a CD and delivered to the customer very quickly.

On the other hand, the same company could buy a mailing list file from another agency or third party and import the data into their database and save the time of having to key the data into the database or an application.

So how can the import and export in SQL Server data tools that are provided by SQL Server be used to get data into a database quickly?

In order to use the import and export in SQL Server Data functions, right click on the server in Enterprise Manager that you would like to use as a basis for the Import or Export.

Then select the Import Data or Export Data menu option from the All Tasks selection as shown in the image below:

Import and export in SQL Server

The interface for Import Data and Export Data is the same, each presents the user with different options for the destination and source for the data involved in the operation.

Regardless of what operation you wish to conduct when transferring the data, an import or an export, each option will provide you with the functionality you need.

For clarification, regardless of whether you want to import or export data, the options are provided that will allow an import or an export to occur once the operation is initiated. Once the selection is made, the following window appears:

Import and export in SQL Server

Select the Next button to continue with the Import/Export Wizard. This will bring up the following screen:

Import and export in SQL Server

This screen will allow the user to select the data source, which is a drop down list of all the types of data that can be used with the import/export.

These include many different formats, not just SQL Server data. The source type can be SQL Server, Access, Oracle or any other data store that can be accessed via ODBC as well. After the data source type is selected, the user is then presented with the specifications needed to specify the location of the source data.

Each option comes with different parameters needed to locate, connect and open the data source. Once the data source is configured, click next which will allow the selection of the destination for the data.

In most instances you will be required to input information that will allow the database to verify who you are and securely control access to that particular repository of data. Each type of data has differing parameters and must be experimented with in order to learn the full functionality of the import/export capabilities of SQL Server and the tools it offers. The following graphic illustrates this screen:

Import and export in SQL Server

This screen is used the same way as the previous one used to input the source of the information for the import and export in SQL Server.

It functions the exact same way the source screen works except that it represents the connection used to access the destination database. If the user wants to go back to the source screen, click the back button. Clicking next will bring up the next screen.

This will allow the user to select the type of objects that they want to move.

Import and export in SQL Server

The user can select to move tables, use a query to specify the data or move data and objects between databases.

Selecting the copy tables option will allow the user to select tables to move in their entirety to the destination. The use of the query option will allow the user to enter a query to specify the data that needs be moved. This can be used to limit the fields or the amount of data being moved.

The transfer objects option will allow the selection of objects to be moved between SQL Server databases.

All of these options are very important when trying to determine how to import and export data in SQL Server. After the choice is made, the user can click next to move on to the next screen, which is shown below.

Import and export in SQL Server

To continue with the import and export in SQL Server operation, this screen will allow the selection of the source and destination objects and the data transformations that need to occur in order to move the data to the correct location.

These transformations can be a straight copy or they can be the result of data manipulation via scripts in order to make the data fit the format you need or transform the data into other elements. Once this is complete, click the Next button, which will bring up the following screen.

After this information is input, our import/export operation is almost ready to run on the server to complete the movement of the data.

Import and export in SQL Server
This will allow the user to run the import/export now or save the configured import/export as a DTS package to be run later or saved on the server to be run as needed.

You may be asking yourself why this utility to import and export in SQL Server is so advantageous, imagine if you had to use data entry tasks in order to manually enter a list of thousands of records of data.

How much time could be saved if the data could be loaded via a file in the import/export data utility?

Experience has proven to me that this tool can prove to be invaluable if the developer or database administrator becomes familiar with the power that it provides. It has saved me numerous hours on projects and has been used exclusively for data transformation tasks that would not have been possible without a tool such as this.

Moving data using the import and export in SQL Server can prove very useful along with the rest of the DTS features in SQL Server.

Return to SQL Server DBA

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / 2019
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.