The SQL Server Import and Export Wizard allows you to quickly and easily import information into a SQL Server 2014 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 2014 already installed. Alternatively, if you’re already running SQL Server Management Studio, follow these steps to launch the wizard:
- Open SQL Server Management Studio
- 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)
- Click Connect to connect to the server from SSMS
- Expand the Databases folder under the name of the server in the left-hand pane.
- Right-click on the name of the database you wish to use and select “Import Data” from the Tasks menu
Importing Data to SQL Server 2014
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:
- Open SQL Server Management Studio
- 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)
- Click Connect to connect to the server from SSMS
- Expand the Databases folder under the name of the server in the left-hand pane.
- Right-click on the name of the database you wish to use and select “Import Data” from the Tasks menu
- Click Next to advance past the wizard’s opening screen
- Choose Microsoft Excel as your data source
- Click the Browse button, locate the address.xls file on your computer, and click Open
- Verify that the “First row has column names” box is checked
- Click Next to advance past the Choose a Data Source screen
- On the Choose a Destination screen, select SQL Server Native Client as the data source
- Choose the name of the server that you want to import data into from the Server Name drop-down box.
- Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode
- Choose the name of the specific database you want to import data into from the Database drop-down box
- Click Next to continue
- Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen
- 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”
- Click Next to move past the Select Source Tables and Views screen
- Click the Finish button to skip ahead to the verification screen
- After reviewing the SSIS actions that will take place, click the Finish button to complete the import. Depending upon the size of the file you are importing, this may take a few minutes. If you are using our sample Excel file, it should only take a few seconds.
Exporting Data from SQL Server 2014
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:
- Open SQL Server Management Studio
- 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)
- Click Connect to connect to the server from SSMS
- Expand the Databases folder under the name of the server in the left-hand pane.
- Right-click on the name of the database you wish to use and select “Export Data” from the Tasks menu
- Click Next to advance past the wizard’s opening screen
- Choose SQL Server Native Client as your data source
- Choose the name of the server that you want to export data from in the Server Name drop-down box.
- Verify the authentication information and select the options corresponding to your SQL Server’s authentication mode
- Choose the name of the specific database you want to export data from in the Database drop-down box
- Click Next to continue
- Choose Flat File Destination from the Destination dropdown box
- 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 to advance past the Choose a Destination screen
- Click Next to accept the “Copy data from one or more tables or views” option on the Specify Table Copy or Query screen
- Click Next to advance past the Configure Flat File Destination screen
- Click Next to move past the Select Source Tables and Views screen
- Click the Finish button to skip ahead to the verification screen
- After reviewing the SSIS actions that will take place, click the Finish button to complete the import.
- When the export completes, verify that the file exists in your selected location. You may open it using any text editor.
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!