topic Inserting data from a SAS Data Set into Oracle using Pass through facility in SAS Procedures

Inserting data from a SAS Data Set into Oracle using Pass through facility

spoorthy — Tue, 25 Feb 2014 16:36:24 GMT

Good morning,

I am trying to use a SQL Pass through facility to insert SAS dataset values into Oracle and i get the following error

ORA-00942: table or view does not exist.

My syntax looks similar to below

proc sql;

connect to oracle(user=xxxxx password=xxxxx path=xxxxx);

execute(

insert into oracletable as select * from sastable

) by oracle;

disconnect from oracle;

quit;

I am wondering if there is a way to use sas data set to insert into ORACLE using SQL Pass through Facility.

Any help would be appreciated.

Thanks!

Re: Inserting data from a SAS Data Set into Oracle using Pass through facility

DBailey — Tue, 25 Feb 2014 16:38:58 GMT

You can't reference a sas table within a pass through block. You can create a library and insert records that way.

libname olib oracle user=xxxx password=xxxx path=xxxx;

proc sql;

insert into olib.oracletable (col1, col2...)

select col1,col2.... from sastable;

quit;

There are also bulk insert options available as well.

Re: Inserting data from a SAS Data Set into Oracle using Pass through facility

spoorthy — Tue, 25 Feb 2014 16:51:32 GMT

Thank you for the response. I am familiar with the SAS/ACCESS way of doing it. But my problem was the first column in the oracle table is a sequence generated ID. Is there a way of inserting data into ORACLE using SAS/ACCESS way where the first column is a sequence generated by oracle and the rest of the columns are from a sas data set?

Updated version of my syntax is as below

proc sql;

connect to oracle(user=xxxxx password=xxxxx path=xxxxx);

execute(

insert into oracletable (ID, col1) select * schema.nextval from dual, select col1 from sas dataset

) by oracle;

disconnect from oracle;

quit;

Re: Inserting data from a SAS Data Set into Oracle using Pass through facility

DBailey — Tue, 25 Feb 2014 17:57:55 GMT

Most of the time, I've seen the oracle column ID value have a default value of the next sequence number so all you would have to do is not include that column in the insert statement. If the column has no default value and you can't add one, I would next look at creating a temp oracle table which would allow you to execute the insert via pass through.