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$]')




2 comments:

Ruby Claire said...

Please write on SQL Server Data Transformation Services, in your next post.


Statement forms

GuildWars2Items said...

If I could rearrange the alphabet, I'd put Y and I together Runescape Gold, A lifetime of happiness! No man alive could bear it, Rune Scape Gold it would be hell on earth Buy Runescape Gold.

If you lose your way, your forever friend guides you and cheers you on Diablo iii Gold, if you turn and walk away, your forever friend follows Buy Diablo iii Gold, your forever friend gets you through the hard times, the sad times, and the confused times Cheap Diablo iii Gold.