How to create a stored procedure that generates a CSV file from SQL Server

Question

I want to generate a .CSV file based on data in a datatable. I know this question has been asked before, but I can't find any examples of how to specify what the separator should be.

For example if I have a table and a query like this, this is what I want the output to be:

MyTable:

Id - Int Key
NickName- NvarChar
REALName - NvarChar
Number - NvarChar
Updated - bit

Query:

SELECT * 
FROM MyTable 
WHERE Updated = 1

Output:

I want my output to use | as the field separator. So the output in the CSV file will look something like this:

Id|NickName|REALName|Number|Updated
1|NickNameHere|RealNameHere|0798548558|1
2|NickNameHere2|RealNameHere2|079948558|1

and so on.

Are you using 3 different versions of SQL Server?

Forklift
– Forklift

2017-02-21 17:58:01 +00:00
Commented Feb 21, 2017 at 17:58 — Forklift
– Forklift, Commented Feb 21, 2017 at 17:58
Use BCP to create the CSV file.

SS_DBA
– SS_DBA

2017-02-21 18:23:33 +00:00
Commented Feb 21, 2017 at 18:23 — SS_DBA
– SS_DBA, Commented Feb 21, 2017 at 18:23

marc_s · Accepted Answer · 2017-03-01 20:08:14Z

1

The following query generate CSV data with '|' separator

select 'Id|NickName|REALName|Number|Updated'
union all
select
       cast (Id as nvarchar) + '|'
       + NickName  + '|'
       + RealNameHere  + '|'
       + Number + '|'
       + cast (Updated as nvarchar)
    from MyTable 
    WHERE Updated = 1

Saving output results to TextFile:

Method 1: from within SSMS

From ssms menu: query -> results to -> results to file

Method 2: using Powershell Invoke-SqlCmd

 Invoke-SqlCmd -Query "your query" | Export-Csv "path\to\csvfile"

Method 3: using SqlCmd command line tool:

sqlcmd -q "your query" -o "path\to\csvfile" -S server -P password -d database

edited Mar 1, 2017 at 20:08

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 21, 2017 at 21:59

M.Hassan

11.2k5 gold badges74 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ayo Adesina Over a year ago

but how do I get that to output/create a CSV file, surely this is just the select.

Collectives™ on Stack Overflow

How to create a stored procedure that generates a CSV file from SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related