Join Excel spreadsheet to a SQL database

Question

I often get requests that look something like this:

"Hey, can you reconcile this Excel spreadsheet with our database and 
fill in the additional info for the ones you find? We also need to know 
which ones are missing."

ID     Name     Client     AdditionalInfo
------------------------------------------
234    John     Client1
147    Mike     Client2
546    Mary     Client3
...
(Several hundred or thousand more rows)

Is there some way to take an Excel spreadsheet like that and join it to a SQL database within SQL Server Management Studio? I usually copy the ID to make a massive IN list, which is obviously far from ideal. Then I still have the task of lining up the data if I don't get a 1:1 match for every row.

I'm using SQL Server 2005 and Excel 2010.

I've tried to add an Excel spreadsheet as a linked server but querying it warned me that 'Ad Hoc Distributed Queries' are blocked and must be enabled. I'm not sure what negative implications enabling that may have, so I didn't go any further down that route.

My next thought was to use Excel formulas to make a SELECT [Col_A], [Col_B], [Col_C] UNION ALL for every single row in Excel, then copy that to SSMS to make a temp table I could join... but that still seems like a fairly messy and inefficient hack

Answer 1

You could import your Excel spreadsheet into a table in your database created specifically for that reason. In SSMS, right-click your database, go to Tasks->Import Data and choose Excel as your Data Source.