In this short Blog post let’s see how to fetch results in a Custom/pipe delimited format file directly from SSMS without any fancy SSIS Packages involved. In fact you can use SSMS for fetching result set as with any delimited value.
In the above screenshot I pulled few columns from “AdventureWorks” Database with the default Settings. You can copy the results including column headers and paste into excel and create a delimited file. Or if you are looking for CSV or Tab Delimited file, you can achieve without much pain by just right click in the result set and choosing “Save results as” and selecting the required file format. Let’s assume the requirement is to create a Pipe(|) delimited file with column names as the first row in the file, or some other custom delimiter in the file. You can’t see those options while saving the results by default. But, it’s very simple to achieve this just by turning on few options in your SSMS.
Go to Tools->Options->Query Results->SQL Server->Results to Text as shown below
In the top right corner drop down the list and select Custom delimiter as shown below.
Once you choose Custom Delimiter, you’ll have an option to enter the Delimiter as you can see below.(I entered “|” for Pipe Delimited files in this example)
Click Ok and now the trick is to select “Results to Text” in your SSMS while executing the Query and you get a result set with a clean pipe delimiters as you can see below:)
Or choose “results to file” and execute the same to save the results directly to a file. You can see in the below Screenshot where I’ve selected the required option in my SSMS and saving the file as “Pipe”.
Now, I’ve navigated to that folder and opened the file with notepad where I got the file exactly as I was expecting to be as you can see below :)
As you can see we don’t need any fancy tools to get custom delimited results…All it takes is exploring our own SSMS :)
Hope this helps!