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:
- Write the query
- Export to a CSV
- 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
- Use BCP (https://nakulvachhrajani.com/2011/05/02/bcp-amp-bulk-inserts-underappreciated-features-of-microsoft-sql-server/)
- Edit the query to include comma-separators and then copy-paste to notepad
- 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:
- Right-click in the Query editor window containing the query to be executed
- Choose “Query Options”
- In the Query Options window, navigate to the “Results” node and click on “Text”
- Change the “Output format” to “comma delimited”
- Now press “Ctrl + T” or go to Query –> Results to –> Text
- 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
Isn’t this great? Have SSMS take care of all your troubles, and take the saved time to read something new on Just Learned!
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.