How to upload data from excel using Sql Query

Today i will show you here how to upload data from excel using Sql Query.
 First run the following code after running SQL SERVER as admin



  Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' 1;
GO

then from excel file to your table. Remember Column name must be the same in table and
Excel Sheet.

 Example with query....

INSERT INTO [dbo].[EmpInfo](EmpCode, JoiningDate, ConfirmationDate, BirthDate, Designation, Grade)
 SELECT EmpCode, JoiningDate, ConfirmationDate, BirthDate, Designation, Grade
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=E:\Development\Payroll\Document\Date\Joining & Confirmation Date & Birthdate & Job Grade & Designation (1).xlsx','SELECT * FROM [Sheet1$]');