3 Ways To Export an SQL Query to an Excel File using SQL Management server

Export SQL Data to Excel from Microsoft SQL Server

Let’s go over three ways to export an SQL Query to an Excel File Using MSSQL 

Despite the pushback from Database aficionados, sometimes it makes sense to export data from SQL to an Excel file. It really depends on who is the audience of the information. Not everyone is great with SQL Analysis. Few people require access to the database.

And lots of times the boss just needs to see the information in Excel.

So, if Excel is the Output required this article is here to help you Export SQL Queries to Excel. 

Here are three ways to Export a SQL query to Excel format. The last one can be kind of tricky. I’ll provide a condensed version followed by a detailed version with screenshots.

Three quick ways to Export SQL Data to Excel:

Choose the Summarized Version and then scroll to further down to use the SQL Export Version to Excel that works best for you. 

Educative

Method Number 1 – Copy Grid results and Paste into Excel

Under Query, Make sure results to Grid are selected.
After Running your query, right-click the top right corner of the grid.
Copy With Headers.
Paste into an Excel File

Possible Issues:
I’ve seen formatting issues with this strategy. For Example, there are situations where the spreadsheet drops preceding zeroes after the copy-paste.

I’ve also noticed lengthy fields, (think of a really long free text field) end up running into the next line.

For the reasons above, I prefer this next method for a clean Excel file.

Method Number 2: Export the Query Results as a Task

In the object explorer window, find the database you want to export from.
Right Click the Database, Click Tasks, and Export Data
The Wizard walks you through the rest of the steps but I have included screenshots below.

Method Number 3 – Use Good Old fashioned TSQL to send the data to an excel file

For those who value speed above all use the following script format.

INSERT INTO OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0; Database=C:\SQL2019\Reports\Usernames.xlsx;’,’SELECT * FROM [Sheet1$]’) SELECT DisplayName FROM dbo.Users Where Reputation > 2000

Possible Issues – Configuring this might not be your type of fun and getting this straight deserves its own article.

 

Step by Step instructions with screenshots

keepersecurity.com

Method Number 1 – Copy Grid results and paste into Excel

After ensuring results to grid turned on, Execute your query, right-click the top left-hand corner of the results grid.

Copy Grid results and paste into Excel

Choose Copy with Headers and then you are ready to paste in Excel with Ctrl + C

Headers

Method 2 – Export Via the Export Wizard

Right-click on the database you want to export from. Then Select tasks and “Export Data”.

Export Data

The SQL Server Wizard will startup. Click Next through the prompts.

SQL Server Wizard

Select the appropriate Native client, your server name, and database and choose “Next”.

server name and database

Next, Select Microsoft Excel and the file path where you want to import the data. The .xls file that you name will be created by this process.

Microsoft Excel

Now you can choose to export a whole table or a query. For the purpose of this exercise, we are creating a query.

creating a query

Paste the query into the SQL Statement field. Make sure every output field has a unique name.

SQL Statement

Click Next on the “Select Source Tables and Views” screen.

Select Source Tables

I use the default settings on the “conversion issues and data type mapping screen”

data type mapping screen

Now you can choose to Run immediately or Save an SSIS Package for later reuse.

SSIS Package

Double Check your settings and click finish.

Make sure there were no errors in the Export.

Export Wizard

Now Go to the directory you choose earlier and make sure you have a sweet-looking Excel File at that location!

Excel File

Method Number 3 – Use TSQL to send the data to an excel file

This method is the quickest once you get it set up but the configuration is the tricky part. Permissions can be a limiting factor.

Also with the script below, you have to make sure the file exists before you run the query for it to import properly.

First, create a blank excel file at the directory of your choosing.

C:\SQL2019\Reports\Usernames.xlsx

Then run this script below.

INSERT INTO OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,’Excel 12.0;
Database=C:\SQL2019\Reports\Usernames.xlsx;’,’SELECT * FROM [Sheet1$]’)
SELECT DisplayName FROM dbo.Users Where Reputation > 2000

Configuring this can be tricky and dependent on your level of permissions. Make sure you have the correct Linked Server/Provider installed (‘Microsoft.ACE.OLEDB.12.0’) And check your Database user settings to this server .

 
keepersecurity.com

Latest Blog Posts

Learn about SQL joins using Excel

Learn about SQL joins using Excel

Why do we join tables in SQL? Tables that logically represent information are the foundation of relational databases. The ancient rules of database normalization remind us to arrange database tables to reduce redundancy and increase flexibility. An adequately...

Super Bowl Squares Template

elink.io | See Original The Superbowl is almost here. If you are tasked with creating blocks for a pool, there's still time to pull out your ruler, draw up a grid, and wrangle a bunch of friends into writing their names in the squares. Or, spare yourself some time...

How to Return Multiple Matches in one Cell Using a lookup?

VLOOKUP is great for looking up a value and returning another corresponding value. But what happens when the first match just isn't enough. I've been tasked with finding a way to put return not just the first but the second, and third match. At first, I tried to...

How to Delete every other Row in Excel

How to Delete every other Row in Excel

Have you ever needed to delete every other row in Excel? You are not alone.   There are a few ways to accomplish it. It's a pretty good candidate for a VBA solution, but I'm going to show you how to quickly delete every other row without using VBA or a plugin. I...

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Microsoft Excel has roughly 750 million users all over the world. It is an essential, all-purpose business tool. This spreadsheet software can fill a wide range of business needs. Every business professional needs to get familiar with Excel. Your proficiency level...

Select the Email Service Provider that is right for you.

Select the Email Service Provider that is right for you.

  Excelshortcut.com commits to continuous improvement. I've wondered if our Communication distribution process is lacking. Is there a more effective tool to help us share valuable content? Mailchimp has been ExcelShortcut.com's tool for email creation and...

Planning Software
Share This