Simple way to import data into SQL Server


MENU
By:
Overview

SQL Server Management Studio (SSMS) provides the Import Wizard task which you can use to copy data from one data source to another.  You can choose from a variety of source and destination data source types, select tables to copy or specify your own query to extract data, and save your work as an SSIS package.  In this section we will go through the Import Wizard and import data from an Excel spreadsheet into a table in a SQL Server database. 

Explanation
To begin launch SSMS by clicking SQL Server Management Studio from the Microsoft SQL Server program group.  Upon launching SSMS you will be prompted for a connection; connect to the Database Engine. 

Locate the MSSQLTipsSSISTutorial database in the SSMS Object Explorer (create the database if necessary); if the Object Explorer isn't visible click the View menu then select Object Explorer:

bject explorer tutorial db

Right click on the MSSQLTipsSSISTutorial database in the Object Explorer, select Tasks, then Import Data from the context menu to launch the Import Wizard.  Click Next to advance past the Welcome dialog (if shown).  In the following sections we will walk through the wizard step-by-step.

Choose a Data Source

The Choose a Data Source dialog allows you to specify the source of your data.  Fill in the dialog as shown below to specify an Excel spreadsheet as the data source:

mport wizard data source

Click Next to proceed to the Choose a Destination dialog.

Choose a Destination

The Choose a Destination dialog allows you to specify the destination data source for the data you are importing.  Since we are running the Import wizard, the dialog will be displayed with the values already filled in as shown below (based on the database you right clicked to start the Import wizard):

mport wizard destination

Click Next to proceed to the Specify Table Copy or Query dialog.

Specify Table Copy or Query

The Specify Table Copy or Query dialog allows you to choose whether to import data by selecting tables and/or views from the data source or specifying a query to extract data.  Select Copy data from one or more tables or views as shown below:

mport wizard specify tables or query

Click Next to proceed to the Select Source Tables and Views dialog.

Select Source Tables and Views

The Select Source Tables and Views dialog allows you to select the tables and views that you want to import.  For our demonstration we are going to select the DimGeography table as shown below:

mport wizard specify tables

You can click the Preview button to view the first 100 rows of the data in the data source as shown below:

mport wizard preview

You can click the Edit Mappings button (on the Select Source Tables and Views dialog) to review the column mappings from the data source to the data destination as shown below.  If the table does not exist in the destination data source, you can also edit the mappings.

mport wizard column mappings

You can click the option to drop and recreate the table in the destination data source; by default this option is unchecked.  You can click Enable identity insert if the destination table has an identity column and the data source has values that you want to assign to the identity column.  If the table does not exist in the destination data source, the Create destination table radio button will be selected and the other radio buttons will be disabled.  If the table already exists in the destination data source,  the Create destination table radio button will be disabled and you can select either the Delete or Append options.

You can click the Edit SQL button to review and/or edit the SQL to create the table in the destination data source as shown below:

mport wizard create table sql

Click OK twice to return to the Select Source Tables and Views dialog, then click Next to proceed to the Save and Execute Package dialog.

 Save and Execute Package

The Save and Execute Package dialog gives you options to perform the import operation and to create an SSIS package and save it to SQL Server or the file system as shown below:

mport wizard save and execute

For our purposes we will save the SSIS package.  Click Next to proceed to the Save SSIS Package dialog.

Save SSIS Package

The Save SSIS Package is invoked if you chose to save your import operation as an SSIS package on the Save and Execute Package dialog.  Fill in the dialog as shown below:

mport wizard save

Click Next to proceed to the Complete the Wizard dialog.

Complete the Wizard

The Complete the Wizard dialog shows a summary of the options that you have chosen for the import operation as shown below:

mport wizard complete

Click Finish to execute the SSIS package.  You can open the imported table in SSMS and view it.  A portion of the table is shown below:

mport excel to sql server


Last Update: 10/22/2009

<< Previous

Next >>



Comments For This Article




Thursday, January 3, 2019 - 12:52:39 PM - Ray Barley Back To Top (78617)

To correct the error The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Install the Microsoft Access Database Engine 2016 Redistributable.  You can download it from this URL:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Read the instructions before you install, especially the Additional Information.

Thursday, January 3, 2019 - 5:41:32 AM - santosh Back To Top (78611)

showing error given below while trying to add excel file

TITLE: SQL Server Import and Export Wizard

The operation could not be completed.

ADDITIONAL INFORMATION:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

------------------------------

Thursday, June 21, 2018 - 3:16:07 AM - Ahmed Back To Top (76265)

 

This article was helpful.

Thanks for posting.

Tuesday, November 21, 2017 - 3:54:10 AM - Dayo Back To Top (70044)

 

 

Thank you very much for this

Saturday, November 4, 2017 - 6:26:34 PM - Laila Back To Top (69215)

Thank you for this valuable article !

Monday, October 23, 2017 - 1:18:22 PM - emily Back To Top (68697)

 This is for th eintial load but what if you are adding records to an existing table?

 

Wednesday, August 9, 2017 - 3:34:17 PM - Melissa Back To Top (64399)

This was a great knowledge base article with simple to understand language.  Used a flat file source type and was able to adjust my data type mapping. Great instructions!

 

Tuesday, July 11, 2017 - 12:00:22 AM - Reena Bhatia Back To Top (59224)

 TITLE: SQL Server Import and Export Wizard

------------------------------

 

Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE `Address` (

`AddressID` Long,

`Addr..." failed with the following error: "The Microsoft Office Access database engine could not find the object 'Address'.  Make sure the object exists and that you spell its name and the path name correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

 

------------------------------

BUTTONS:

 

OK

------------------------------

 

 

Monday, July 3, 2017 - 2:44:19 PM - Greg Robidoux Back To Top (58785)

Hi Steve,

there is not an MSSQLTipsSSISTutorial database to install.  Based on the info above you would need to create a new database that can be use for the tutorial.  Here is a link that you can use to create a testing database called MSSQLTipsSSISTutorial: https://technet.microsoft.com/en-us/library/ms186312(v=sql.105).aspx

After the new empty database has been created, you can then import the data and the import process will create a new table in the database.

-Greg

Monday, July 3, 2017 - 12:56:46 PM - Steve Begin Back To Top (58779)

 

Like John T., 16 months ago, I too do not have MSSQLTipsSSISTutorial in my Object Explorer. Since that seems to be the necessary starting point, could you please help me understand either how I can obtain that - or if there is another way? I am logging into a database server at my school where I have been given access to various databases for my exclusive use. Into one of them did I want to use this tutorial to import a CSV and create its corresponding table automatically. Thanks in advance for any guidance.

Wednesday, June 21, 2017 - 6:48:36 AM - madhu Back To Top (57853)

 

 Thanks worked for me

Friday, June 16, 2017 - 3:10:00 PM - tad Back To Top (57474)

 Doesn't work if you don't have AccessDatabaseEngine installed.

Monday, March 20, 2017 - 12:42:27 AM - vothaison Back To Top (51446)

 

WOW.

Thank you.

Thursday, December 1, 2016 - 12:33:23 AM - Amit Ghorpade Back To Top (44869)

 

 

Thank you so much....!!

Friday, August 19, 2016 - 6:22:55 AM - Nag Back To Top (43145)

 This article really helped me alot. Thank you.

 

Thursday, August 11, 2016 - 4:53:06 PM - Janet Back To Top (43107)

 Thank you for publishing this.  It is very useful and easy to follow.

 

Saturday, April 16, 2016 - 5:56:31 PM - John Treloggen Back To Top (41242)

 

 

I do not have MSSQLTipsSSISTutorial database in the Object Explorer window in SQL Server Management Studio, which I have downloaded to my laptop.

I need to import an excel spreadsheet.  Is ther another way to do it without  MSSQLTipsSSISTutorial database?  Can I download MSSQLTipsSSISTutorial database, if I can find it?  I've searched the internet with no luck.  Thanks, John

Tuesday, March 22, 2016 - 2:45:56 AM - Milan Back To Top (41024)

 Thanks for publishing this article its very helpful.

 

Friday, March 18, 2016 - 8:28:09 PM - Russell Tye Back To Top (41000)

 this is old stuff. Can we talk about sql 2014 and integration services catalog topics

 

Wednesday, February 10, 2016 - 6:51:10 AM - Venkatesh Guptha Back To Top (40637)

 hi -

This article is really good and very usefull

Thanks for publishing this

 

:):):) 














get free sql tips
agree to terms