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"?
Rolle has asked in a comment:
Well, this should unload the column headers, so it is expected to be only one row, isn't it?
Here's a sample with an unload from the system table sysusertype:
unload select list('''' || name || '''', ',' order by column_number) from sa_describe_query('select * from sys.sysusertype order by type_name') to 'C:\MyFile.txt' quotes off escapes off; unload select * from sys.sysusertype order by type_name to 'C:\MyFile.txt' quotes on append on;
Note the "manual" quoting of the first unload: I have used list with single quotes around the column names to build the following result (which is one single string value, not a sequence of strings):
Then I have used the QUOTES OFF and ESCAPES OFF clause to unload that as-is - the default QUOTES ON would double the single quotes and would insert a leading and trailing quote (as the result set is just one single value), as seen here:
ESCAPES OFF is necessary when using QUOTES OFF as otherwise the comma is replaced by its hexadecimal representation:
Finally, with QUOTES OFF ESCAPES OFF and the second UNLOAD appended it should give something like:
'type_id','creator','domain_id','nulls','width','scale','type_name','default','check','base_type_str','extended_base_type_str' 1,'type_id',1,'smallint','smallint',2,0,2,,,'ut',24,1,'SYS','ISYSUSERTYPE','type_id',0,0,, 2,'creator',21,'unsigned int','unsigned int',4,0,4,,,'ut',24,2,'SYS','ISYSUSERTYPE','creator',0,0,, 3,'domain_id',1,'smallint','smallint',2,0,2,,,'ut',24,3,'SYS','ISYSUSERTYPE','domain_id',0,0,,
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.
answered 28 Feb '16, 07:10
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.)