Linked Server With Excel.

In this part I’ll help you in creating a Linked Server with Excel. Or simply how to import/query Excel in Sql Server.

In Sql Server 2005 you need to enable the 'Ad Hoc Distributed Queries'. For that first execute the following scripts.
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

And now here go
1.
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=E:\Mangal.xls;Extended Properties=Excel 8.0')...Sheet1$
2.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', Sheet1$)
3.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Here Excel 8.0 means this is applicable to Excel 2002, Excel 2000, or Excel 97 workbook.
Database means path of your excel book and its name.

Now you can do any Sql query with excel that you usualy do with any table. For example you can join excel with other tables, you can insert excel rows in other tables.
Lets say you want to insert first 3 columns of Excel sheet into table Customers.
You can do
INSERT INTO customers (CustId, CustomerName, Address )
SELECT Custid, CustomerName, Address
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Or to join a table with Excel

SELECT A.ColumnName, E.ColumnName
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]' ) E
INNER JOIN Sql_Table A
ON a.id=e.id

- Mangal Pardeshi.