Import Excel Data into Mssql using SQL Statement

you need to run one line per line, if you run all SQL together.
you will get below error message:
Incorrect syntax near 'sp_configure' 


Please Follow below Step 
Step 1: sp_configure 'show advanced options', 1
Output Message: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


Step 2: reconfigure
Output Message: Command(s) completed successfully.


Step 3: sp_configure 'Ad Hoc Distributed Queries', 1
Output Message: Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.


Step 4: reconfigure
Output Message: Command(s) completed successfully.


Step 5: Run your SQL to import Excel Files


Insert Excel Data into New Table (Create New Table)

INSERT INTO myTableName

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

Insert Excel Data into Existing Table

SELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

SQL Code to import Excel Data into New Table in Database


sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

SELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')

SQL Code to import Excel Data into Existing Table in Database

sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

SELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')