Running SQL Queries in Excel

[Dave7]

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!

[#2]

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?

[#3]

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. 

[#4]

@Dave7 

Power Query is not the tool for generating SQL query, you may only embed already prepared SQL query into the connector to SQL database. There are pros and cons, but mostly the latest. If only performance for relatively complex queries since SQL query runs on server site, but even in this case it's better to create view directly on the SQL server and connect to it.

In Power Query major part of tasks could be done with user interface without coding on M-script (internal Power Query language). Query to SQL database could be generated from UI as well.

[#5]

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.

[#6]

@Wyn Hopkins 

Hi, Wyn and @Dave7 I know this is an old post but not sure if you resolved this I tried to write the script in the previous post but got this error.

Let me know what you think of this error, please.

Thanks in advance.

[#7]

@casecase 

That's on server site - certificate used is not recognised, thus connection can't be installed.