how to import an excel file into sqlserver 2008

Question

How can i import an excel file into a new table in sqlserver2008 express edition using an sql query without using the import wizard

Thanks Prady

Answer 1

There is a microsoft knowledge base article that lays out all the ways this is possible.

http://support.microsoft.com/kb/321686

I think using OPENROWSET or OPENDATASOURCE will be the easiest way, without the wizard. (see Distributed Queries)

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

See OPENROWSET documentation, with examples lower down the page.

http://msdn.microsoft.com/en-us/library/ms190312.aspx