Excel spreadsheets can be imported into SQL server in various ways. The simple and quickest methods are with using Linked Servers and Distributed queries. The difference is how the connection is used. Linked servers uses a persistent configured connection where as Distributed queries uses a dynamic connection. Linked servers are not suitable when we want to import more than one document. For every document there a linked connection should be configured(painful).
You can configure Linked connection through Server Objects->Linked Servers-> New Linked Connection (See below image) from the Management studio object explorer. Use select statement with OPENQUERY to read excel data.
Select * from OPENQUERY(<linkedserver>, SheetName), remember sheetname always must be ended with $ and is in the form of [SheetName$]
Select * from OPENQUERY(EXCELDATA, [Sheet1$])
This one works well both in SQL Server 2005 & 2008 as long as Jet OLE DB provider installed. Please be aware that it will not work on 64 bit OS as no equivalent 64 bit OLE db drivers are available.
With Distributed Queries
Use OPENROWSET command instead of OPENQUERY. You can specify above connection properties in OpenRowSet. See documentation for syntax. For more information, follow these link http://support.microsoft.com/kb/321686
1) When you install Sql server 2008 Management studio, it provides two import options one for 32 bit and 64 bit in program files. So if somehow JET ole db import is not possible, still you can use one of these options for importing excel data. Better use CSV formats.
2) While importing with JET OLE DB, file must not be in Use otherwise you will receive the below error
OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.