How can i create pipe delimited txt file from sql query results

Question

How can i create pipe delimited txt file from sql query results

tswathi21

Old Hand

Points: 385
More actions
April 29, 2012 at 6:46 am

#260627

HI all,
pls can you answer me it's urgent task..i searched in online but no luck yet...

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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 '

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!

tswathi21 Old Hand Points: 385 More actions · Answer 2

Hi Lowel,

Thanks for ur quick response ..can i do that in master database because i'm a tester i don't know ..can i do that or not..

Here is my database details..can you answer accordingly..

My db name:Max_Test

My table name: #temp.

I need these columns from that table are offer_id,reservation_num,personal_id,acct_nbr_he.

PLs reply TIA.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

tswathi21 (4/29/2012)
Hi Lowel,
Thanks for ur quick response ..can i do that in master database because i'm a tester i don't know ..can i do that or not..
Here is my database details..can you answer accordingly..
My db name:Max_Test
My table name: #temp.
I need these columns from that table are offer_id,reservation_num,personal_id,acct_nbr_he.
PLs reply TIA.

You want that with or without column headers?

--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)

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

Never mind. We'll do it both ways.

Since you're a tester and don't know much about SQL Server or SSMS, we'll do this as simply as possible.

Paste the following code into SSMS on same window that create the #Temp table, highlight the code, and then press the f5 key. Copy the output from the results and paste into notepad. Save the notepad file.

--===== Comment out from here through UNION ALL

-- if you don't want column headers.

SELECT 'Offer_ID' + '|'

+ 'Reserveration_Num' + '|'

+ 'Personal_ID' + '|'

+ 'Acct_Nbr_HE' + '|'

UNION ALL

SELECT CAST(Offer_ID) AS VARCHAR(8000)) + '|'

+ CAST(Reserveration_Num) AS VARCHAR(8000)) + '|'

+ CAST(Personal_ID) AS VARCHAR(8000)) + '|'

+ CAST(Acct_Nbr_HE) AS VARCHAR(8000)) + '|'

FROM temp

;

--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)

tswathi21 Old Hand Points: 385 More actions · Answer 5

Hello Jeff,

Thanks for ur time and quick response..

Actually I did in otherway ..

In SSMS- tools-options--there i select results to text ..and changed the custom delimited to | ..and i ran that it worked for me..Saved the results to a file....

Thank you so much again...