Bulk Loading for Sybase IQ

Bulk loading is the fastest way to insert large numbers of rows into a Sybase IQ table. To use the bulk-load facility, specify BULKLOAD=YES. The bulk-load facility uses the Sybase IQ LOAD TABLE command to move data from the client to the Sybase IQ database.

Here are the Sybase IQ bulk-load data set options. For detailed information about these options, see Overview.

In this example, the SASFLT.FLT98 SAS data set creates and loads FLIGHTS98, a large Sybase IQ table. For Sybase IQ 12.x, this works only when the Sybase IQ server is on the same server as your SAS session.

libname sasflt 'SAS-library';
libname mydblib sybaseiq host=iqsvr1 server=iqsrv1_users
    db=users user=iqusr1 password=iqpwd1;

proc sql;
.create table mydblib.flights98
       (bulkload=YES)
        as select * from sasflt.flt98;
quit;

When the Sybase IQ server and your SAS session are not on the same server, you need to include additional options, as shown in this example.

libname sasflt 'SAS-library';
libname mydblib sybaseiq host=iqsvr1 server=iqsrv1_users
    db=users user=iqusr1 password=iqpwd1;
proc sql;
create table mydblib.flights98
 (  BULKLOAD=YES
   BL_USE_PIPE=NO
    BL_SERVER_DATAFILE='/tmp/fltdata.dat'
    BL_CLIENT_DATAFILE='/tmp/fltdata.dat' )
 as select * from sasflt.flt98;
quit;

In this example, you can append the SASFLT.FLT98 SAS data set to the existing Sybase IQ table, ALLFLIGHTS. The BL_USE_PIPE=NO option forces SAS/ACCESS Interface to Sybase IQ to write data to a flat file, as specified in the BL_DATAFILE= option. Rather than deleting the data file, BL_DELETE_DATAFILE=NO causes the engine to leave it after the load has completed.

proc append base=mydblib.allflights
     (BULKLOAD=YES
       BL_DATAFILE='/tmp/fltdata.dat'
       BL_USE_PIPE=NO
       BL_DELETE_DATAFILE=NO)
data=sasflt.flt98;
run;