How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016

This article demonstrates the inbuilt functionality of importing data from Microsoft Excel to a SQL table using import export wizard.

This article demonstrates how to import the data from a Microsoft Excel sheet to a SQL Server table in Microsoft SQL Server 2016. This method of transferring data from source (Excel) to destination (SQL Server) looks very easy for experienced developers but this is helpful for beginners who has just started their career in SQL Server.

There are many scenarios where you will get the master data from a client in Excel sheet format and we need to load this data into the SQL server. Now, you might be thinking how can we import data in a SQL table from Excel without writing a single line of query to the database. So, here in this article, I will demonstrate how we can use SQL Server's inbuilt functionality import and export wizard and load data in the table.

Here, I have created a sample data using an Excel sheet which contains employee detail data. 

How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016

 

Let’s start step by step. Here I have one Excel sheet with employee details data with six columns as above and this detail I will use as an example and load into employee table.

Step 1

Run and search for SQL server import and export data.
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Step 2
 
Click on import and export wizard and click on next,
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Step 3
 
Once you click on next you will get an option to select data source from where you want to load data. As in the below screen, select data source as Microsoft Excel from drop down list and click on next.
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Step 4
 
Select Excel sheet from folder where your Excel sheet is with Employee data using browse button.
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Step 5
 
Select destination from drop down list as below. Here I have selected SQL server as destination because we want import data in sql server. 
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Step 6
 
Click on next button and select server name and database name.
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Note
In the above screen we have two options for authentication but here I am going with Windows authentication. If you are using SQL server authentication, then you have to provide user name and password.
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Step 7
 
Here we have two options to import data; first is you can copy directly  to table and the other option is to write a query and insert it into table. I have chosen the first option and I have the below screen.
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Step 8
 
Click on Edit mapping button to set your mapping like data type, nullable and size for table column, highlighted with red circle.
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Step 9
 
Click on Next button and we are ready with a sucessful message for our Excel sheet to load employee table.
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 
Finally I have loaded employee data to table Employee of EmployeeDataBase. I have run a select query on employee table and am able to see all data which I have in source file (Excel).
 
How To Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016
 

Conclusion

 
In this article I have demonstrated how we can import data from Excel to database table using import export wizard. I hope this will help you to import data without writing a single SQL statement to insert data. If you have any questions please feel free to ask, It will really help me to to improve.