DATA Step, Macro, Functions and more

How to query two databases at once.

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

How to query two databases at once.

I want to do something like this:

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


Accepted Solutions
Solution
‎07-27-2017 11:19 AM
Super User
Posts: 5,424

Re: How to query two databases at once.

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?
Data never sleeps

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: How to query two databases at once.

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.

Contributor
Posts: 46

Re: How to query two databases at once.

the teradata part is GB's.

New Contributor
Posts: 3

Re: How to query two databases at once.

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

Contributor
Posts: 46

Re: How to query two databases at once.

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???
Solution
‎07-27-2017 11:19 AM
Super User
Posts: 5,424

Re: How to query two databases at once.

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?
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 94 views
  • 0 likes
  • 4 in conversation