unload table with column names

[Former Member]

I am new to Sybase, hopefully someone can help me with this problem.

SELECT * FROM temp_data order by r_date, r_time;
OUTPUT TO 'C:\\files\\headerfile.txt' 
    FORMAT TEXT
    DELIMITED BY '\\x09'
    QUOTE ''
    ENCODING 'UTF-8'
    WITH COLUMN NAMES;

This code works for me but unfortunately WITH COLUMN NAMES; can not be used in a stored procedure. Then I tried:

unload select location, r_date, r_time, ct_codel_code_title from temp_data 
    TO 'C:\\files\\headerfile.txt'
    FORMAT TEXT
    DELIMITED BY '\\x09'
    QUOTE ''
    ENCODING 'UTF-8'

still the column name don't appear. Can anyone help?

[VolkerBarth]

A possibly even more "future proof" way would be to use sa_describe_query() with the "real query" as argument to get the column list of the result set - that would even work with ad-hoc queries and would guarantee that the column order is correct.

Here's a sample for the original query:

select list(string('"',name,'"'),char(09) order by column_number)
from sa_describe_query('SELECT * FROM temp_data order by r_date, r_time');

Yes, in case the "real query" contains quotes, they will have to be masked, as usually...

---

Note: sa_describe_query was introduced in v10, so that won't work for older versions - in contrast to Justin's suggestion.

[VolkerBarth]

FWIW, this FAQ deals with a similar (though more general) problem, and Mark has created a bunch of samples how to create a function that does export both column headings and data, where details of the the format (delimiters and the like) can be set as parameters...

Post Processing Result Set with Dynamic Query Input