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?

[johnsmirnios · Accepted solution]

The OUTPUT TO statement is interpreted by dbisql -- it is not understood by the server and therefore cannot be used in a stored procedure.

The WITH COLUMN NAMES option is not supported by UNLOAD. An enhancement request has been made for this feature. In the meantime, you might be able to manually writing the column names out with one UNLOAD statement and then writing the data with UNLOAD ... APPEND ON.

[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

[justin_willey]

If you were wanting to "future proof" against additional columns being added in future, you could select the column names from the SYS.SYSCOLUMNS view - something like:

select list(string('"',cname,'"'),char(09) order by colno asc)
  from sys.syscolumns where creator = 'dba' and tname = 'temp_data' ;

Ordering by colno should ensure that your columns are listed in the same order as the result set produced by select *