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:

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:

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

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:

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.

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.

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.

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