SSMS – Query Result configuration – Generate comma separated output without BCP or query changes


I have maintained that SSMS has a lot of underappreciated features, and that most of the community is not utilizing SSMS to it’s maximum potential. Today, I will show you how a simple configuration change helped in speeding up one of the most common tasks – generating a csv from data stored in a table.

I had a requirement to extract some data out of a Microsoft SQL Server database, save it as a CSV (comma-separated value file) and then send it across to another team for further analysis. The logical order in which I addressed the task was:

  1. Write the query
  2. Export to a CSV
  3. Send the CSV to the team in need

Write the query

Writing the query was simple, and for this example, let’s assume I came up with the following query:

USE AdventureWorks2008R2
GO
SELECT     Sales.SalesOrderHeader.SalesOrderID, Sales.SalesPerson.TerritoryID, HumanResources.Employee.BusinessEntityID, Sales.SalesOrderHeader.OrderDate, 
                      Sales.SalesOrderHeader.DueDate, Sales.SalesOrderHeader.Status, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderHeader.OnlineOrderFlag, 
                      Sales.SalesOrderHeader.PurchaseOrderNumber, Sales.SalesOrderHeader.SalesOrderNumber, Sales.SalesOrderHeader.CustomerID, 
                      Sales.SalesOrderHeader.AccountNumber
FROM         HumanResources.Employee INNER JOIN
                      Sales.SalesPerson ON HumanResources.Employee.BusinessEntityID = Sales.SalesPerson.BusinessEntityID INNER JOIN
                      Sales.SalesOrderHeader ON Sales.SalesPerson.BusinessEntityID = Sales.SalesOrderHeader.SalesPersonID
WHERE     (Sales.SalesOrderHeader.OnlineOrderFlag = 0)
ORDER BY Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesPerson.TerritoryID DESC

Export to a CSV

When it comes to exporting to a CSV file, I have multiple options

  1. Use BCP (https://nakulvachhrajani.com/2011/05/02/bcp-amp-bulk-inserts-underappreciated-features-of-microsoft-sql-server/)
  2. Edit the query to include comma-separators and then copy-paste to notepad
  3. Use SSMS to generate the comma-separated output and directly copy it over to notepad

I will not be considering the point about using BCP because it has already been addressed by my previous post, and second because the server I was originally running on was production instance and bcp was not an option available to us.

Option #2 – Edit the query to include comma-separators and then copy-paste to notepad

We can modify the query to something similar (please note, there can be multiple, much better implementations. The script below is for demonstration purposes only).

USE AdventureWorks2008R2
GO
SELECT     Sales.SalesOrderHeader.SalesOrderID, ',',
           Sales.SalesPerson.TerritoryID, ',',
           HumanResources.Employee.BusinessEntityID, ',',
           Sales.SalesOrderHeader.OrderDate, ',',
           Sales.SalesOrderHeader.DueDate, ',',
           Sales.SalesOrderHeader.Status, ',',
           Sales.SalesOrderHeader.ShipDate, ',',
           Sales.SalesOrderHeader.OnlineOrderFlag, ',',
           Sales.SalesOrderHeader.PurchaseOrderNumber, ',',
           Sales.SalesOrderHeader.SalesOrderNumber, ',',
           Sales.SalesOrderHeader.CustomerID, ',',
           Sales.SalesOrderHeader.AccountNumber, ','
FROM         HumanResources.Employee INNER JOIN
                      Sales.SalesPerson ON HumanResources.Employee.BusinessEntityID = Sales.SalesPerson.BusinessEntityID INNER JOIN
                      Sales.SalesOrderHeader ON Sales.SalesPerson.BusinessEntityID = Sales.SalesOrderHeader.SalesPersonID
WHERE     (Sales.SalesOrderHeader.OnlineOrderFlag = 0)
ORDER BY Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesPerson.TerritoryID DESC

The problem that we run into is alignment. If the results are copy-pasted into Notepad, they would not turn out as expected, and some manual formatting will be required.

Options #3 – Use SSMS to generate the comma-separated output and directly copy it over to notepad

All this custom query editing may not be possible if this is the output of a stored procedure (changing the stored procedure may break the application). Instead, the simplest option is to run through the following:

  1. Right-click in the Query editor window containing the query to be executed
  2. Choose “Query Options”
  3. image
  4. In the Query Options window, navigate to the “Results” node and click on “Text”
  5. Change the “Output format” to “comma delimited”
  6. image
  7. Now press “Ctrl + T” or go to Query –> Results to –> Text
  8. Execute the query. Notice that the output is ready to be copy-pasted directly into Notepad as a CSV file – NO formatting operations need to be done
  9. image

Isn’t this great? Have SSMS take care of all your troubles, and take the saved time to read something new on Just Learned!

Benefit:

The benefit of the method shown above is that it is specific to the particular query window. So, once the query window is closed, SSMS will start honouring the default settings under Tools –> Options.

Other cool SSMS productivity features:

I have written about other cool productivity improvements in SSMS here: Some underappreciated productivity features of SSMS. Also, you can search through my blogs for more content on SSMS & it’s utilities.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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