Run SQL code which is in a spreadsheet

Ok, I don't really know how to title or categorize this one, but here it is..

I have a spreadsheet with an SQL query in it. below is a super basic example;
sqlonspreadsheet.PNG
The query will always be one column wide, but can have tens or hundreds of thousands of rows (a lot of INSERT statements)
What I want to do is run that query and dump the results into another sheet (within the same file)
Is this possible? If so, what angle should I attack this from..?

Double posted, please delete

Note to mod: i solved the problem i reported on this post.

A better way to see what I am trying to do is:



sqlinexcel2.PNG
I want to be able to type a column in there as my SQL statement.

OR

I want to use the contents of a Text File as my SQL statement (i can export the query in the cells to a text file with vba pretty easily)


Hope that clarifies things a little...

What's the database you are connecting to?

It is a company intranet database, why do you ask?

Because the connection string would depend on what type of database you are trying to access.

I have the server set up already in Data Sources (ODBC) if that's what you mean. I can pull results into excel using a query no problem, I am mostly looking for a way for the query itself to change on the fly.

My query is generated with formulas, for example in B2 I would have:

Please Login or Register  to view this content.

Column A contains the numbers I need to match data for in SQL

At the bottom of all the 'inserts' i have a select statement which joins my table to a few others and retrieves data relevant to the values in Column A.


Normally, I would just copy all the rows of data in B and dump them into SMSS, then copy the results back to excel and index/match them all together, I am looking for a quicker way, and think that if Microsoft Query can look at column B's values, instead of a hard-coded query, it would save a lot of time.


Hope this makes sense.

I wouldn't use a querytable for that since you aren't returning data. I'd simply connect to the database using ADO (or DAO) and then execute the query you want. That requires the appropriate connection string for the database, be it Oracle or SQL Server or whatever.

I am returning data though;

My query creates a temp table in SQL, inserts a list of values into that temp table, and then a select statement (within the same query) joins my temp table to numerous tables in the DB, returning the required data to match to the source. "MyTable" should actually be "#MyTable", didn't know if that was important.

The MyTable is created each time the query runs, and is deleted each time the connection is closed.