SQL query results into Excel

It is easy to pull data from a MS SQL table or view into excel, the UI is very straight forward for that. But that is not the case when you want a custom sql query to be the one pulling data from the database. 

To do this in Excel 2010, you need to first set up the connection to the database and then set up the query:

Setting up the connection

image

image

 

image

This is where you set the server location, login, AND the database you want to query:

image

Leave the default table empty:

image

Double click your newly created data source

image

Building the Query

The screenshot below pops up automatically when you double click the data source (see screenshot above). We don’t want to add any tables:

image

We want to use our hand written SQL:

image

Enter your SQL:

image

Chances are you’ll run into this, hit ok

image

Your query has been built, hit the exit button:

image

Tell Excel where to put the data from your query:

image

You’re done!

You can now right click on any cell that has data from your query and hit Refresh when you want Excel to pull fresh data from the database:

image

Advertisements