CODEWITHSUNDEEP
sqlsasteradata
DS R
DS R

Reputation: 235

Connecting to Teradata via SAS (SQL Explicit Passthrough), for data pull, is it recommended to use execute statement?

I have seen two options:

  1. Not using execute statement

    libname lib "/dir";
run;

proc sql ; 
CONNECT TO TeraData (Server = 'edw' User =&tduser pass=&tdpass Database = UDW Mode = TeraData);            
create table lib.datanew as

select * from connection to teradata 
(select a.name,b.age from table1 a left join table2 b on a.pkey=b.pkey); disconnect from teradata; quit;

  2. Using execute to create a multiset volatile table in Teradata and then bringing it to SAS library

    libname lib "/dir";
run;

proc sql;
CONNECT TO TeraData (Server = 'edw' User =&tduser pass=&tdpass Database = UDW Mode = TeraData);
execute(    create multiset volatile table datanew as

    (select a.name,b.age from table1 a left join table2 b on a.pkey=b.pkey)
        with data primary index (name) on commit preserve rows
    )
BY TeraData;

CREATE TABLE lib.datanew AS (SELECT * FROM CONNECTION TO TeraData (SELECT * FROM datanew));
disconnect from teradata;
quit;

I just want to understand if one way or the other can be faster? If so, why?

Upvotes: 1

Views: 1815

Answers (2)

DS R
DS R

Reputation: 235

Answering part of my question.

What I found is that when I need to create multiple volatile tables within Teradata, using the already created ones, 2) is the normal choice; I can run the proc sql commands, as if, I were in the Teradata SQL assistant environment.

Upvotes: 0

Sanek Zhitnik
Sanek Zhitnik

Reputation: 726

So, when you use execute statement, you use pass through facility (Docs). That mean that your sql code will be executed right in DBMS, and sas will only get the result table.

More examples you can see in pdf

Also SAS Community has a discussion .

Upvotes: 1

Related Questions