How to Import and Export Data With SQL Server 2012

Database development

Stefan Matei Lungu / Getty Images

What to Know

  • In SQL Server Management Studio, enter details, click Connect, right-click the database, and click Import Data.
  • To import, select Import Data > Next > Excel > Browse, open the file, and follow the steps to import data from the file.
  • To export, select Export Data > Next > SQL Server Native Client, and follow the steps to export the data.

This article explains how to import and export data with SQL Server 2012.

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 an 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.

  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. Click Next.

  5. Choose Microsoft Excel as the data source (for this example).

  6. Click the Browse button, locate the address.xls file on your computer, and click Open.

  7. Verify that the First row has column names box is checked. Click Next.

  8. On the Choose a Destination screen, select SQL Server Native Client as the data source.

  9. Choose the name of the server that you want to import data into from the Server Name dropdown box.

  10. Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode.

  11. Choose the name of the specific database you want to import data into from the Database dropdown 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.

  12. In the Destination dropdown 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.

  13. Click the Finish button to skip ahead to the verification screen.

  14. 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 dropdown 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 dropdown box. Click Next.

  9. Choose Flat File Destination from the Destination dropdown box.

  10. 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.

  11. Click Next twice more, then Finish to skip ahead to the verification screen.

  12. After reviewing the SSIS actions that will take place, click the Finish button to complete the import.

The SQL Server Import and Export Wizard allows you to import information easily into an 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.

Format
mla apa chicago
Your Citation
Chapple, Mike. "How to Import and Export Data With SQL Server 2012." ThoughtCo, Jan. 4, 2022, thoughtco.com/sql-server-2012-import-export-wizard-1019797. Chapple, Mike. (2022, January 4). How to Import and Export Data With SQL Server 2012. Retrieved from https://www.thoughtco.com/sql-server-2012-import-export-wizard-1019797 Chapple, Mike. "How to Import and Export Data With SQL Server 2012." ThoughtCo. https://www.thoughtco.com/sql-server-2012-import-export-wizard-1019797 (accessed March 19, 2024).