Using Excel in a query

I am often asked to import data in to SQL Server. On a good day, I will be given the data in an Excel file which relates very well to a database table. On a bad day, it will come in a Word document and this will often involve a lot of copying and pasting before the data can be extracted.

On the good days, I have been using the DTS tool in Enterprise Manager to upload the data in to a table which then allows me to use it in whatever way I want. Using the OLEDB drivers allows a query running on SQL Server to join to an Excel document. Today I discovered how to use Excel as part of my T-SQL query using Distributed query.

In the example below I am using the Excel spreadsheet managers to link to my table.

	SELECT u.[region 1], s.full_name AS RegionalManager
	FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\managers.xls;Extended Properties=Excel 8.0')...Updates$
	JOIN staff s
	on s.id = u.staff_id

view raw example1.sql hosted with ❤ by GitHub

I can now use the Excel spreadsheet as a normal table. A simple change to this query and I can insert the data from my spreadsheet in to a table.