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.


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.