Use local table to filter data from SQL server

I have a very large SQL table (2M rows) which I want to import to an Excel table.
I want to limit the returned data to those rows where a foreign key field in the SQL data is found in an Excel list of about 2000 items

Nothing ive tried seems to work

Any suggustions?
MikeHillsi2gAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike in ITIT System AdministratorCommented:
What have you tried? It'll be easier not to give you the same answers if you can tell us what you have already done.

My first thought is to import the Excel list into your SQL query and use it to do the export. There are a couple of ways to do it as mentioned on this link, but the simplest is with an openrowset or opendatasource like this:

Opendatasource:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$

Open in new window


openrowset:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Yes, certainly getting the ~2000 values into a  MSSQL query is the best approach. Using OpenRowset et. al. requires to have access to the Excel file from the server. Usually MSSQL does not have network access, and it's too much to expect the file can be copied to the server machine prior to running the SQL ...
So the question is: would that work for you?
0
Jan LouwerensSoftware EngineerCommented:
You could also try taking those 2000-ish ids, and making a comma separated list from them, then using that in an in clause:

SELECT * FROM veryLargeTable WHERE foreignKeyId IN (id1, id2, id3, ...);
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
1   Per the above experts change 'is found in an Excel list of about 2000 items' to a SQL Server table that contains these items/whatever.
2   Create a SQL Server Stored Procedure to return a query that JOINs on the above table.  Test to make sure it is what you expect.
3   Read my article Microsoft Excel & SQL Server:  Self service BI to give users the data they want to get an idea on how to connect Excel to that stored procedure, and even the table that contains your 'list of about 2000 items' if you want.  The article is a little outdated as it was created pre-2012 and PowerBI suite, but you'll get some excellent ideas.

Good luck.
Jim
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
What version of SQL ? 64-bit ? Office / Excel Version ?

Jet engine might not work depending on versions... Might need updated office/sql drivers.

Are you wanting to push to Excel from SQL or, pull from SQL inside Excel ?

Are you intending to create a new spreadsheet ?
0
MikeHillsi2gAuthor Commented:
WOw
Thanks for rapid feedback.
All answers contributed.

1. The best solution would be for SQL to access the required list but - as anticipated - the server was unable to see the spreadsheet.

2. The second best solution would be to send the list to the server - I created a stored procedure that used a Table-valued parameter and got it to work within the server (rather than using an IN list as suggested)
I guess I need to create a VBA to achieve this from within Excel using guidance from Jim Horne https://www.experts-exchange.com/articles/13675/Microsoft-Excel-SQL-Server-Self-service-BI-to-give-users-the-data-they-want.html

3.The third solution - which I am currently using -is to use Power Query but is not ideal
I downloaded the complete SQL table to the Data Model and constructed a combine query to join it to the required list. (Thank you M is for Data Monkey book from www.powerquery.training/book)
Works for my immediate need but clearly not a long term solution.

I will be pursuing option 2 for future requirements
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.