How to write formatted text file in T-SQL?

I need to create formatted text file in the following format:

HDR - Header Record
DET - Individual Detail Records (1 or more rows) containing the children records
FTR - Footer Record indicating the end of that transaction with a numeric indicating how many detail (Child) records should have been read for that header.

For Example (A single parent with 3 child records);

"HDRvalue1,value2,value3,value4,value5"
"DETvalue6,value7,value8"
"DETvalue6,value7,value8"
"DETvalue6,value7,value8"
"FTR3"

The Header and Detail data is retrieved from two tables: Header and Detail. I have no problem to retrieve data line by line but when I try to write the line into text file the file maste..xp_cmdshell and bcp overwrite the file. Is there any way to use append mode. I am not aware of this mode for BCP. Or is there any other way to accomplish this in T-SQL? Thank you.
BurzhuinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I've done this in past projects where I had to output a text file with multiple detail sections.

The way I did it was to create a table with two columns:  sort_order (tinyint) and detail (varchar(8000).

Insert your header row into column detail, with a sort order of 1.
Insert your detail rows into column detail, with a sort order of 2.
Insert your footer rows into column detail, with a sort order of 3.

Then use xp_cmdshell, bcp, or whatever to export to text file the following SQL (saved as a view or just the T-SQL, your call)

SELECT detail FROM your_table ORDER BY sort_order

Also, typically I leave your_table alone to facilitate any prod support help on it, until the beginning of every time I use it, when I do a TRUNCATE TABLE your_table.

Good luck.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BurzhuinAuthor Commented:
I tried to insert multi columns row into single column using straight INSERT and got an error. Do I miss something?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'got an error', preferably with the T-SQL code that produced it.  
Mind readers we ain't.

The most likely reason is that all values need to cast as varchar inorder to concatenate them into a single varchar.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

BurzhuinAuthor Commented:
This is the error I got:

Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Show us the T-SQL.

The above error will occur if there are less rows in the SELECT then there are in the INSERT, such as..
INSERT INTO SomeTable(fruit, animal, number)   -- 3 columns
SELECT 
   TheFruit, 
   TheAnimal, 
   TheNumber, 
   this_column_creates_an_error -- <--  Looky here, this makes 4 columns
FROM MyTable

Open in new window

0
BurzhuinAuthor Commented:
I have to insert into one column multiple columns row. I have never done it before and cannot see any other way as to assign every column to a different variable and combine them into one and pass it as string. But is there shorter and easier way to insert multiple columns row into one Table's column?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Something like <an overly-simplified air code>
INSERT INTO some_table (detail varchar(8000), sort_order tinyint) 
SELECT 'HDR' + char_value + CAST(int_value as varchar(10)) + 'foo', 1 as sort_order
UNION ALL
SELECT 'DTL' + char_value + CAST(int_value as varchar(10)) + CAST(date_value as varchar(10)), 2
UNION ALL
SELECT 'FTR3', 3

Open in new window

... Then to select the data set....
SELECT detail FROM some_table ORDER BY sort_order

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your text file.  -Jim
0
BurzhuinAuthor Commented:
Thank you. I jumped into writing the stored procedure and forgot to say Thank you. I do apologize for it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.