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