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.
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.
Step 2
Click on import and export wizard and click on next,
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.
Step 4
Select Excel sheet from folder where your Excel sheet is with Employee data using browse button.
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.
Step 6
Click on next button and select server name and database name.
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.
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.
Step 8
Click on Edit mapping button to set your mapping like data type, nullable and size for table column, highlighted with red circle.
Step 9
Click on Next button and we are ready with a sucessful message for our Excel sheet to load employee table.
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).
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.
Nagarajan PPosted Feb 14, 2021, 5:48 PM
Hi sir , if the approach allow Primary key constraints and all .. or we need set the primary key After import the data?
Rohit SinghPosted Feb 2, 2021, 10:03 AM
This blog is very informative and helps me know how to Import Data From Excel Data Into SQL Table In Microsoft SQL Server 2016.
Zuhair SazaliPosted Mar 16, 2020, 10:29 PM
Hi sir. For step 8, how to change the type of the source to be same with destination? My source type is VarChar while destination type is varchar
Jignesh KumarPosted Mar 20, 2019, 6:17 AM
Thank you Sourav kumar. Glad to hear from you
Sourav Kumar DasPosted Mar 20, 2019, 6:09 AM
Hi Sir, Such a superb Article on Importing data from excel to SQL Server