How to Import and Export Data With SQL Server 2012

Using the Import and Export Wizard

Database development
Stefan Matei Lungu / Getty Images

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

  • Microsoft Excel
  • Microsoft Access
  • 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

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 want to manage and 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 want to use and select Import Data from the Tasks menu.

Importing Data to SQL Server 2012

The SQL Server Import and Export Wizard guides you through the process of importing data from any of your existing data sources to a SQL Server database. This example walks through the process of importing contact information from Microsoft Excel to a SQL Server database, bringing the data from a sample Excel contacts file into a new table of a SQL Server database.

Here’s how:

  1. Open SQL Server Management Studio.
  2. Provide the details of the server you want to manage and the appropriate username and password if you’re not using Windows Authentication.
  1. Click Connect to connect to the server from SSMS.
  2. Right-click on the name of the database instance you want to use and select Import Data from the Tasks menu. Click Next.
  3. Choose Microsoft Excel as the data source (for this example).
  4. Click the Browse button, locate the address.xls file on your computer, and click Open.
  1. Verify that the First row has column names box is checked. Click Next.
  2. On the Choose a Destination screen, select SQL Server Native Client as the data source.
  3. Choose the name of the server that you want to import data into from the Server Name drop-down box.
  4. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode.
  5. Choose the name of the specific database you want to import data into from the Database drop-down box. Click Next, then click Next again to accept the Copy data from one or more tables or views option on the Specify Table Copy or Query screen.
  6. In the Destination drop-down box, choose the name of an existing table in your database or type the name of a new table that you want to create. In this example, this Excel spreadsheet was used to create a new table called "contacts." Click Next.
  7. Click the Finish button to skip ahead to the verification screen.
  8. 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 guides you through the process of exporting data from your SQL Server database to any supported format.

This example walks you through the process of taking the contact information you imported in the previous example and exporting it to a flat file.

Here’s how:

  1. Open SQL Server Management Studio.
  2. Provide the details of the server you want to manage and 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 want to use and select Export Data from the Tasks menu. Click Next.
  5. Choose SQL Server Native Client as your data source.
  6. Choose the name of the server that you want to export data from in the Server Name drop-down box.
  7. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode.
  8. Choose the name of the specific database you want to export data from in the Database drop-down box. Click Next.
  1. Choose Flat File Destination from the Destination drop-down box.
  2. Provide a file path and name ending in “.txt” in the File Name text box (for example, “C:\Users\mike\Documents\contacts.txt”). Click Next, then Next again to accept the Copy data from one or more tables or views option.
  3. Click Next twice more, then Finish to skip ahead to the verification screen.
  4. After reviewing the SSIS actions that will take place, click the Finish button to complete the import.