Best way to move sybase IQ table to Sybase ASE

bcpmigrationsybase-asesybaseiq

I use bcp to copy Sybase IQ table to sybase ASE server. Actually bcp first creates some bulk file with extracdet sybase IQ table there, and after just loads that file to Sybase ASE. Is it the one and the best (considering accuracy, speed) way to do that?

Best Answer

IQ has the ability to output data directly from the data server to a local filesystem. It's faster than returning data over the network to a client tool. Create the output file that way, and then bulkcopy it into Sybase ASE using bcp -c :

set temporary option Temp_Extract_Name1='my_output_file';
-- Set column tab delimiter via hex value for use by bcp -c
-- Note, character between the single quotes needs to be an actual
-- tab character. \t and \0x09 don't work.  Might need to be 
-- in a script where you can insert a tab character using vi or 
-- something similar.  Stupid IQ.
set temporary option TEMP_EXTRACT_COLUMN_DELIMITER=' ';
select * from my_table;
set temporary option Temp_Extract_Name1='';

This should produce a file that in a suitable format for bcp with the -c option. Be careful about tabs embedded in the data. I'm not sure if they'll be escaped.

Related Question