OUTPUT TO, WITH COLUMN Names in ASA 9

[former_SQLA_member1694895]

Hi

I am using the OUTPUT TO function in ISQL and it all works fine.

When I add in the 'WITH COLUMN NAMES' the query doesn't work. I am using ASA 9, is it not possible to export column names in ASA 9?

SELECT *
FROM dba.table1
ORDER BY table1.part_number ASC;
OUTPUT TO 'c:\\\\filename.csv'
    FORMAT ASCII
    QUOTE '"'
    WITH COLUMN NAMES

If not is there a workaround for exporting the column names?

[VolkerBarth · Accepted solution]

The WITH COLUMN NAMES clause was added to the OUTPUT command with v12, so it's clearly not available with v9.

A workaround is to use two queries:

• A first query to export the column names, such as

    select list(string('"', cname, '"') order by colno asc)
    from sys.syscolumns where creator = 'dba' and tname = 'table1';
    output to 'c:\\\\filename.csv' format ascii quote '"';

• Your original query as second one - and OUTPUT used with the APPEND option

    select * from dba.table1
    order by table1.part_number asc;
    output to 'c:\\\\filename.csv' format ascii quote '"' append;

(Have not tested the queries lacking v9, but you should get the point...)