Best Method to Query SQL using Variable Excel Values

Question

There's an SQL database that I would like to query through excel without having to pull the entire SQL database into excel (5Million + rows). I have established the connection in excel. The values that I will be using to query the SQL Database are variable (typically around 150-200 cells).

End Result: The variable cells in excel are all in column A, I would like to query the Column A SQL values to retrieve the Column B SQL value and pull them back into excel. I know I could download the whole SQL database into excel and do a vlookup but my excel file will undoubtedly crash with all the SQL data.

Does anyone know where I should start? Would this best be resolved through VBA code or the advanced editor directly in excel?

Cheers,

Brandon M

ekzekz · Accepted Answer · 2020-05-13 16:49:21Z

1

You can include "?" in the query text of your connection. The first time you run the query, Excel will ask you what each of the "?" references. You can then change the values in those cells, and refresh the connection to use those new values.

answered May 13, 2020 at 16:49

ekzekz

261 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Ankit Sharma · Accepted Answer · 2020-05-13 16:58:09Z

0

Your situation is a bit unclear to me. Do you want to perform "Select * from table where column in (Cell A)"? and then to print into Cell B? If yes, you can use VBA code to build your SQL query and select the data.

If you don't want to use VBA, you can use some cell concatenation to build the query and can pass the query to SQL.

answered May 13, 2020 at 16:58

Ankit Sharma

693 bronze badges

Collectives™ on Stack Overflow

Best Method to Query SQL using Variable Excel Values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related