Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I'm given a list of names and email addresses in an excel spreadsheet. I am asked to find all customers in the database who's name, and email are in that list.

I currently know of two ways to achieve this...

  • Import the data into a new table (to be removed later) and then use this table to get the data.
  • Paste the names and addresses into a SQL query.

Is there a 'better' way to do this kind of thing (i.e. one that doesn't require me to place the data in the database before using it, and doesn't require me to create a monster query that has all the data in a 'where x in (data)' part)

share|improve this question
    
The other obvious option is to "Export" the SQL data to Excel. That is, create a "simple" query that just returns all of the data, then match/filter it in Excel. So "monster" data-size instead of "monster" query. –  RBarryYoung May 1 '13 at 19:39
add comment

2 Answers

Great question! Especially the "monster query" part ;)
The answer is this guy here, enjoy: Excel Data Source as Linked Server

share|improve this answer
2  
Creating a linked server (or even an adhoc open query) works great, but be careful of headers, titles, merged cells, etc. as the data type of an entire column seems to be determined by the first row. –  datagod May 1 '13 at 13:18
add comment

Your option 1 is good as you can just create a staging table called "Excel_Data". Then once you import data in sql server, you can just write a query to just report the data that is both in the database and in the excel spreadsheet. The data loading can be automated by Bulk Insert or using SSIS.

Also, you can use Linked server to directly query the excel spreadsheet as described here.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.