topic Re: Error loading data in teradata volatile table in SAS Programming

Error loading data in teradata volatile table

nickspencer — Thu, 12 Dec 2019 21:27:33 GMT

I am trying to load SAS dataset into a teradata table but I am getting error while using multistmt option with proc append. My program looks like below.

Libname tdwork teradata user=“xxxxx” password=“xxxxx”
tdpid =“xxxx” connection=global dbmstemp=yes;

proc sql;
connect to teradata (user=“xxxxxx” password=“xxxxxx” tdpid=“xxxxx” connection =global);

execute ( create multiset volatile table test_tera
(column1 char(40))
no primary index
on commit preserve rows)
by teradata;
execute (commit work) by teradata;
quit;

proc append base=tdwork.test_tera(multistmt=yes)
data =dataset1;
run;

ERROR: Teradata connection: object ‘test_tera’ does not exist. Correct error and restart as an append process with option TPT_RESTART=YES. Since no checkpoints were taken, if previous run used FIRSTOBS=n, use the same value in the restart.

Am I doing something incorrectly? When I remove multistmt option, it is running forever. I thought multistmt would work with volatile table.

Any help is appreciated

Thanks

Re: Error loading data in teradata volatile table

Tom — Thu, 12 Dec 2019 21:44:49 GMT

How large is the data you are uploading?

When I was working with Teradata I found it to be much faster to load the data into a permanent database since it would allow me to use FASTLOAD option. I even had a macro that would automate loading into a unique permanent table name and then execute Teradata commands to copy it from the permanent database to the volatile table and delete the permanent table.

Re: Error loading data in teradata volatile table

nickspencer — Thu, 12 Dec 2019 21:48:24 GMT

@Tom
I don’t have privilege to create permanent table so I am trying to create volatile table and load data into it.
I know fastload and multiliad won’t work with volatile table so trying to use multistmt but I am getting an error