Forum Discussion
Dave7
Oct 27, 2020Copper Contributor
Running SQL Queries in Excel
I've used ODBC queries in Excel before but it was always with stored procedures on the server. Now I'd like to be able to define freeform SQL queries in Excel that pull data from a MS SQL Server. I have been playing around for 30 mins or so and Googling how to do it but can't seem to find anything. How do I build and debug SQL queries in Excel?
Thanks!
Hi Dave7
The best way to pull data from a SQL Server is to use Power Query in Excel, have you come across that yet?
- Dave7Copper Contributor
Thanks, Wyn Hopkins. I should have said that yes, I got into the Power Query but couldn't figure out how to use it to write plain SQL. The Advanced Editor it has seems to use a different language (PowerQuery, apparently). Is it just not possible to write plain SQL? If that's the case, I'm sure I can figure out PowerQuery though if you have a good primer I'm all ears.
Hi Dave7
When connecting to a SQL database you do have the option of writing plain SQL here...
If you use the Power Query interface to do transformations then if that transformation can be converted into SQL it will be done automatically in the background... You can often (but not always) see this happening by Right Clicking on a step and viewing the Native Query:
Note:
You may well get better performance by using the Power Query steps and allowing it to convert and "fold" much of the query back to the SQL Server.
Writing your own SQL query from scratch breaks "query folding" and the data processing will be carried out on your computer.
e.g. Filtering just for this year's records - if done via applying a filter in Power Query will push that filtering back to be performed quicker by the Server.