Friday, December 23, 2011

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 1sp_configure 'show advanced options', 1
Output Message: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.


Step 2reconfigure
Output MessageCommand(s) completed successfully.


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


Step 4reconfigure
Output MessageCommand(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$]')