Importing and Exporting Data with SQL Server 2012

The SQL Server Import and Export Wizard allows you to quickly and easily import information into a SQL Server 2012 database from any of the following data sources:

• Microsoft Excel
• Microsoft Acces
• Flat Files
• Another SQL Server Database

The wizard builds SQL Server Integration Services (SSIS) packages through a user-friendly graphical interface.

Starting the SQL Server Import and Export Wizard

You may start the SQL Server Import and Export Wizard directly from the Start menu on a system that has SQL Server 2012 already installed. Alternatively, if you’re already running SQL Server Management Studio, follow these steps to launch the wizard:

1. Open SQL Server Management Studio
2. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)
3. Click Connect to connect to the server from SSMS
4. Right-click on the name of the database instance you wish to use and select “Import Data” from the Tasks menu

Importing Data to SQL Server 2012

The SQL Server Import and Export Wizard provides you with a guided process to import data from any of your existing data sources to a SQL Server database. In this example, we will walk through the process of importing contact information from Microsoft Excel into a SQL Server database. In this example, we will bring the data from our sample Excel contacts file into a new table of a SQL Server database.

Here’s how to get started:

1. Open SQL Server Management Studio
2. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)
3. Click Connect to connect to the server from SSMS
4. Right-click on the name of the database instance you wish to use and select “Import Data” from the Tasks menu
5. Click Next to advance past the wizard’s opening screen
6. Choose Microsoft Excel as your data source
7. Click the Browse button, locate the address.xls file on your computer, and click Open
8. Verify that the “First row has column names” box is checked
9. Click Next to advance past the Choose a Data Source screen
10. On the Choose a Destination screen, select SQL Server Native Client as the data source
11. Choose the name of the server that you want to import data into from the Server Name drop-down box.
12. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode
13. Choose the name of the specific database you want to import data into from the Database drop-down box
14. Click Next to continue
15. Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen
16. In the Destination drop-down box, choose the name of an existing table to import the data into a table that already exists in your destination database or type the name of a new table that you wish to create. In our example, we will use this Excel spreadsheet to create a new table called “contacts”
17. Click Next to move past the Select Source Tables and Views screen
18. Click the Finish button to skip ahead to the verification screen
19. After reviewing the SSIS actions that will take place, click the Finish button to complete the import.

Exporting Data from SQL Server 2012

The SQL Server Import and Export Wizard provides you with a guided process to export data from your SQL Server database to any supported format. This example will walk you through the process of taking the contact information you imported into a SQL Server database in the previous example and export it to a flat file.

Here’s how to get started:

1. Open SQL Server Management Studio
2. Provide the details of the server you wish to manage as well as the appropriate username and password (if you’re not using Windows Authentication)
3. Click Connect to connect to the server from SSMS
4. Right-click on the name of the database instance you wish to use and select “Export Data” from the Tasks menu
5. Click Next to advance past the wizard’s opening screen
6. Choose SQL Server Native Client as your data source
7. Choose the name of the server that you want to export data from in the Server Name drop-down box.
8. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode
9. Choose the name of the specific database you want to export data from in the Database drop-down box
10. Click Next to continue
11. Choose Flat File Destination from the Destination dropdown box
12. Provide a file path and name ending in “.txt” in the File Name text box (for example, “C:\Users\mike\Documents\contacts.txt”)
13. Click Next to advance past the Choose a Destination screen
14. Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen
15. Click Next to advance past the Configure Flat File Destination screen
16. Click Next to move past the Select Source Tables and Views screen
17. Click the Finish button to skip ahead to the verification screen
18. After reviewing the SSIS actions that will take place, click the Finish button to complete the import.

That’s all there is to using the SQL Server Import and Export wizard! Feel free to experiment with the wizard yourself, trying a variety of file formats and destinations!