How to output an unstructured file in SQL Server using T-SQL?

Question

In SQL Server using T-SQL how do you output an unstructured file that has a "detail-header", "detail", and "footer" record to one file with a varying comma separated value count?

This is an example of the output structure of the file:

1|header|value0|date
2|detail|value1|value2|value3|date
3|detail|value1|value2|value3|date
4|detail|value1|value2|value3|date
5|header|value0|date
6|detail|value1|value2|value3|date
7|detail|value1|value2|value3|date
8|header|value0|date
9|detail|value1|value2|value3|date
footer|9

Data Example:

1|H|laptops|2013-01-30
2|D|cpu1|disk1|ram1|2013-01-30
3|D|cpu2|disk2|ram2|2013-01-30
4|D|cpu3|disk3|ram3|2013-01-30
5|H|desktops|2013-01-30
6|D|cpu4|disk4|ram4|2013-01-30
7|D|cpu5|disk5|ram5|2013-01-30
8|H|servers|2013-01-30
9|D|cpu6|disk6|ram6|2013-01-30
footer|9

I appreciate any help!

Answer 1

I am going to give an idea what i am thinking please do this and update me, to write data from a query to a file please refer below link

http://ask.sqlservercentral.com/questions/7080/export-results-of-a-query-into-a-csvtxt-file.html

To write the query that will give you out put.

will look something like

Suppose you have 3 tables A,b,c

A have 2 coloums
B have 3 coloums
C have 4 coloums


SELECT {WRITE Your Hedder here} as ROW

UNION

SELECT 1+'|'+2+'|'+3+'|'+4 AS ROW
(SELECT A.1,A.2,Null 3,Null 4 FROM A
UNION
SELECT B.1,B.2,B.3,Null 4 FROM B
UNION
SELECT C.1,C.2,C.3,C.4 FROM C
)

SELECT {WRITE Your Footer here} as ROW

Please keep me posted,

I guess this can be done, But i would have been much easy if you were using ETL tool.

Regards

Ashutosh