Export pipe delimited data without using dts or ssis

Question

Post reply

Export pipe delimited data without using dts or ssis

lulu_lutz

SSCrazy

Points: 2155
More actions
June 26, 2012 at 2:11 pm

#258590

Does anyone have some t-sql to export the results of a query in pipe delimited format? Would powershell be better?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i believe your options are to use bcp, CLR or powershell

bcp + TSQL isgoing to require opening up xp_cmdShell.

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT object_name(object_id) As TbLName,name as ColName FROM SandBox.sys.columns ORDER BY object_name(object_id), column_id" queryout C:\Data\Objects.txt -t"|" -c -T '

you can use a CLR to write to disk from TSQL, I have an example i threw on codeplex: http://sqlclrexport.codeplex.com/

that's going to require you to install a CLR, which may or may not be what you are after.

EXECUTE CLR_ExportQueryToCustomDelim @QueryCommand = 'SELECT * FROM @TableVariable',

@FilePath = 'C:\Data\',

@FileName = '\Results_export.txt',

@IncludeHeaders = 1,

@CustomDelimiter = '|'

and of course powershell, as you already suggested.

i don't have a power shell example available in my snippets yet, sorry.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Lowell (6/26/2012)
i believe your options are to use bcp, CLR or powershell
bcp + TSQL isgoing to require opening up xp_cmdShell.

BWAA-HAAA!!!! I use xp_CmdShell to run PowerShell from T-SQL. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 3

Jeff Moden (6/26/2012)
Lowell (6/26/2012)
i believe your options are to use bcp, CLR or powershell
bcp + TSQL isgoing to require opening up xp_cmdShell.
BWAA-HAAA!!!! I use xp_CmdShell to run PowerShell from T-SQL. 😀

ok but can you use xp_CmdShell to run Powershell, which in turn calls bcp? 😀

That's the ticket!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!