How to query two databases at once.

[cellurl]

I want to do something like this:

proc ??
   create table tmp as 
   select 
      t.*,
      o.*
   from
      teradadata t,
      oracle o
   ;

[RW9]

Well, there is FedSQL:

http://support.sas.com/documentation/cdl/en/fedsqlref/67364/HTML/default/viewer.htm#fedsqlrefwhatsne...

Never used it myself.  What is it your trying to do as that code doesn't seem logical or valid.  Why not just pull in both data sources then process, or pull in the smaller and use that in a query to the larger.

[cellurl]

the teradata part is GB's.

[nirajp91]

If you have a common identifier between the two sources, you can simple do a join/merge operation

proc sql;

  create table test as

  select

    a.id,

    a.name,

    b.age

  from table1 as a

  left join table 2 as b on (a.id=b.id);

quit;

Refer to: https://communities.sas.com/t5/SAS-Procedures/LEFT-JOIN-in-SAS-using-PROC-SQL/td-p/158240

[cellurl]

proc sql;
   connect ... to Teradata;
   connect ... to oracle;
   create table tmp as select * from connection to oracle, Teradata
   (
       select 
           t.*,
           o.*
       from
           Teradata t,
           oracle o
      );

//something like this,   DOUBLE connection. Is that possible???

[LinusH · Accepted solution]

No, not like that.
I'm missing a join criteria in you example.
Your options are joining via SAS implicit pass through , risking that both tables would download to SAS.
If your Oracle table is relative small try to copy it to a TD volatile table so that the join will performed completely in TD.
Or perhaps there is a possibility to connect directly to Oracle from TD?