store SAS table data to oracle database

[Jonison]

Hello, experts.

I have established a model in enterprise guide, and the model results are stored in SAS table. IT create a table in Oracle space with same structure of model results (col names, length etc are same).

would you please provide some suggestions on how to load the data from SAS table to Oracle table? (truncate existing records in Oracle data table first).

Many thanks.

[SASKiwi]

I find deleting rows via SQL passthru is efficient:

proc sql;
   connect to Oracle ( <Oracle connection details> ); 
   execute (delete from MyOracleDatabase.MyOracleSchema.MyOracleTable
           ) by Oracle;
   disconnect from Oracle;
quit;

I also find PROC DATASETS APPEND is good for loading data, but you normally need to tweak the INSERTBUFF = option to get good loading performance:

libname MyOracle Oracle <Oracle connection details> insertbuff = 10000;
proc datasets library = MyOracle nolist;
  append base = MyOracleTable data = MySASLib.MySASTable;
run;
quit;