Have you ever wondered how to export a table’s data as a comma separated string using SQLCMD? In this article, we will explore a simple yet powerful technique to achieve this. This method can be a great learning experience to understand how SQLCMD works behind the scenes.

Let’s start with a scenario where we have a table called [Person] in the Adventureworks2012 database. Our goal is to generate a comma separated string of the FirstName and LastName columns for all rows where the LastName is ‘Ferrier’.

First, we need to create a file that contains the SQL statement to retrieve the desired data. In our example, we will create a file called “Contacts-People.sql” with the following content:

SET NOCOUNT ON
SELECT FirstName, LastName
FROM Adventureworks2012.[Person].[Person]
WHERE LastName = 'Ferrier'

Once we have the input file ready, we can proceed with the following steps:

Step 1: Execute the SQLCMD command with the input file

Open a command prompt and navigate to the directory where the input file is located. Then, run the following command:

sqlcmd -i Contacts-People.sql

This command executes the SQL statement in the input file and displays the result in the command prompt.

Step 2: Narrow the column width for a concise output

If the output columns are too wide, we can use the “-W” option to make them narrower. Run the following command:

sqlcmd -i Contacts-People.sql -W

This command adjusts the column width to fit the data, resulting in a more concise output.

Step 3: Add a comma separator between the columns

To separate the columns with a comma, we can use the “-s” option followed by the comma character. Run the following command:

sqlcmd -i Contacts-People.sql -W -s,

This command adds a comma separator between the columns in the output.

Step 4: Remove the header to get only the column values

If we want to exclude the header from the output and get only the column values, we can use the “-h” option followed by “-1”. Run the following command:

sqlcmd -i Contacts-People.sql -W -s, -h-1

This command removes the header from the output, giving us the desired comma separated string of column values.

Now you have learned an easy and quick way to generate a comma separated string of columns from a table using SQLCMD. Feel free to save the output to a flat file and name it with a .csv extension if needed. If you know any other methods to achieve the same result, please let me know.