2

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)

1
  • 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. May 1, 2013 at 19:39

2 Answers 2

3

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

1
  • 3
    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, 2013 at 13:18
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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