Quote string values before export with xp_write_file

[Former Member]

I have a cursor that adds a lot of fields (tab separated) from a select in a declared variable (declared as long varchar). Then I use xp_write_file to export it to a text file. My question is how I can quote all strings in the variable as I can do with ex. "output to"?

[VolkerBarth]

So you add the results of the query explicitly row-by-row, column-by-column to a "result variable" (i.e. by concatenating the values)?

If so, I guess you will then have to add the quotes explicitly, too.

I have not really understand your requirementy, but if you want a result similar to ISQL's OUTPUT statement, but are within a stored procedure or the like where OUTPUT is not available, you should consider the UNLOAD statement. It has almost the same format options as OUTPUT (though with different syntax, here QUOTE and/or QUOTES ON/OFF), and it can be used anywhere.

Note, it can also be used to write the result set to a variable by using the UNLOAD ... INTO VARIABLE clause, in case you would need to modfy that before doing the output to a file.

[Breck_Carter]

consider the UNLOAD statement.

Yes... the alternative to OUTPUT is generally regarded as UNLOAD, not xp_write_file.

[Former Member]

That's right. I have a Function with a cursor that I lopp row by row to a variable and then export it to a text file with xp_write_file.

How do I export to a text file via the UNLOAD function from a SQL including column names?

[Former Member]

How can I get the column names in the smoothest way, when I use the UNLOAD. I have found this, but I do not understand how to get the column names in the exported file

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'

How to get this in the exported file?

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

[VolkerBarth]

What version do you use?

v17 has introduced the WITH COLUMNS NAMES clause to the UNLOAD statement.

For v16 and below there are basically two methods, methinks:

1. You use two UNLOAD statements, the first to select the column names (i.e. one single row, possibly based on the results of sa_describe_query for the real query) as in your sample. And a second one with the real query and option APPEND ON.

2. Or you use one UNLOAD statement and use an UNION ALL statement within, such as (the untested)

UNLOAD
   SELECT "location", "r_date", "r_time", "ct_codel_code_title"
   FROM
   (SELECT 0 as rowNo,
       "location", "r_date", "r_time", "ct_codel_code_title"
    UNION ALL
    SELECT ROW_NUMBER() OVER (ORDER BY location),
       location, r_date, r_time, ct_codel_code_title
    FROM temp_data) dt
   ORDER BY rowNo
TO ...

Note, for the 2nd approach, you will need to use a method to number the rows within result set so the row with the column names is unloaded first. Here this is done with derived query and the ROW_NUMBER() OLAP function.

[Former Member]

We are using version 16, so it is the first option that is possible when the SQL includes over 80 columner. Do you have a working example where sa_describe_query used? With two UNLOAD statements and APPEND ON on the second...

[Former Member]

This doesen't work:

UNLOAD select list(string(name,'\\x09'),char(09) order by column_number) from sa_describe_query('select * from #tmp')  to 'c:\\temp\\test.txt' encoding 'windows-1252' delimited by '\\x09';

Everything is on the same row.