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

2 thoughts on “SQL query results into Excel

  1. Hi – When I click ‘Refresh’, my results don’t (re)appear. Instead, it just says “Query from [mydatasource]” Has this happened to you? I cannot see a setting in the properties that would be causing this result. Thanks in advance.

  2. I figured it out. I had to add SET NOCOUNT ON because I’m creating a temp table before my output statement. Thanks for the post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s