SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tricks - Copy T SQL Result to Excel

Sometimes, some of the silliest of tricks save so much of time and effort. Very common scenario is to copy a query result to excel. Most take either of the 2 approaches

* Copy the result from result grid of management studio and paste to Excel as shown below


The problem with above approach is sometimes, when one pastes the result to excel, the formatting can go haywire, especially if the result contains carriage return (new line) and commas as shown below.


* Export to excel via export wizard

Little tedious and sometimes getting the mapping right can be a pain.

Over the years, the simplest and effective technique, I have used is running T SQL query directly from excel (Yes, you can do that :)). Not just that, it gives a cleanly formatted result all the time!!! Step by step instructions provided below

How to run TSQL Query from Excel:

Open Excel and move to data tab and click on the icon "Other Data Sources" and select "SQL Server" as shown below.


Enter the server to connect to and the credentials


Select the database against which you want to run the query. Pick a random table (doesn't matter if you are not going to query it. Any table is fine)


Click next and finish (we are not done yet :))



Click on properties



Uncheck background refresh check box



Click on the "Definition" tab and change the command type to "SQL" from "Table"



Paste the query you want to run on the "Command Text" box as shown below



Result would be a nice formatted excel sheet as shown below



From excel once can not only get select query results but stored procedures as well. Excel also servers as an excellent tool to query database when one needs to access the database on a machine that doesn't have SQL Server Management Studio. Hope the tip was useful!!!

SQL and SQL Only

I am Nagaraj (aka Raj), a SQL Server Database Administrator for one of the busiest OLTP systems in Singapore. Being a SQL Enthusiast for over 10 years now, I love to explore and share more and more on SQL Server.

Comments

Leave a comment on the original post [www.blogger.com, opens in a new window]

Loading comments...