Introduction
There are many ways to import data from excel file to SQL Server database. Some of the most common methods are
- SQL Server Integration Services(SSIS)
- SQL Server linked Servers
- Openrowset and OpenDatasource functions
- SQL Server Import and Export wizard
In this article, SQL Server Import and Export wizard method is used to import data from excel file to SQL Server database.
Import and Export wizard
This wizard helps you to import and export data between many popular data formats like excel, spreadsheets, text etc..,
Step 1 : Open SQL Server Management Studio , connect to an instance of SQL Server database engine.
Step 2 : Expand Databases, Right click on the database which you want to import data and select Tasks and choose Import Data option which opens “SQL Server Import and Export Wizard”. Click on next button to continue.
Step 3 : Now settings for Data Source wizard will open. In this wizard, select Microsoft Excel as Data Source. In Excel connection settings, select the excel file that needs to be imported. And select excel version and then click on next.
Step 4 : After Data source details are given, now Destination window will open where the details of Destination Server , database is given.
- Choose Microsoft OLE DB Provider for Sql Server as Destination.
- Select the Server and enter authentication details and choose database.
- Choose Copy data from one or more table or views option in the next wizard.
Step 5 : In the destination column, enter table name.
Step 6 : Select Edit Mappings option for mapping excel columns with Database table columns.
Source represents column name of excel and destination represents column name of excel where excel column is mapped to SQL table column. After mapping select Ok to save mapping details.
Step 7 : Finally, select finish option to import the data. And now the data are imported successfully.
Conclusion
Hence, the data can be moved easily from excel to SQL Server using Import and Export Option.