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]

Just for the record, here's a sample how to use an UNION to select column headers and the "real data" in one go - note that because of the UNION's requirement for compatible types, you will have to cast any non-string data to string, which may or may not be appropriate.

unload select type_id, type_name from
  (select 0 as row_no, 'type_id' as type_id, 'type_name' as type_name
   union all
   select row_number() over (order by type_name), cast(type_id as varchar(255)) as type_id, type_name from sys.sysusertype) dt
order by row_no
to 'C:\\MyFileWithUnion.txt' quotes on;

will return something like:

'type_id','type_name'
'114','city_t'
'110','company_name_t'
'116','country_t'
'103','datetime'
'106','image'

Note that here all values are quoted (since they are selected as strings). The ROW_NUMBER() is handy to generate row numbers which are required to preserve the row order here.

(I guess the approach with two different UNLOADs with APPEND is way easier.)