In this article we will explain how to import data in SQL server database from excel with example.
SQL Server Import and Export Wizard
The SQL Server Import and Export Wizard is based in SQL Server Integration Services (SSIS). You can use SSIS to build extraction, transformation and load (ETL) packages and to quickly create packages for moving data between Microsoft Excel worksheets and SQL Server databases.
SQL Server Import and Export Wizard by one of the following step:
Exporting data from Microsoft Excel worksheet to a SQL Server database
right click on your data base show following Figure.
Data Source: Choose Microsoft Excel from the drop-down menu.
Excel File Path: Type in the Microsoft Excel worksheet operating system path.
Excel Version: Select the version of the Microsoft Excel worksheet.
Click Next to go to the Choose a Destination page the following next Step
Data Source: Choose SQL Server Native Client 10.0 from the drop-down menu.
Server Name: Type the name of the SQL Server instance that contains the source data.
Authentication: Choose authentication mode for the data source connection.
Database: Choose the database that contains the source data.
Click Next to proceed to Select Table Copy or Query page the following next Step
You have two options on this page. You can either select to copy tables and views or to copy the results of a query from the data source. We will be copying all the data from the Excel worksheet to our database. Click the circle next to the words Copy data from one or more tables or views. Click the Next button to continue.
Click the Next button to go to Review Data Type Mapping page following to next step:
This is where you'll see how different data types are mapped between the source and the destination and how any conversions issues will be handled.
Click the Next button to proceed to the Save and Run Package page following to next step:
Click the Next button to proceed to the Save and Run Package page, where you can indicate whether you want to save the SSIS package or run it immediately. You can set the package protection level when you save the package. If the protection level uses a password, provide the password here. For more information about package protection levels, see Access Control for Sensitive Data in Packages.
Select the Run immediately option and click the Next button. This takes you to the Complete the Wizard page .Here you can view the choices you made
Click Finish to run the package.