CODEWITHSUNDEEP
sqlgeektool
Bipin Patil
Bipin Patil

Reputation: 11

How to get combine result for 2 different Query of different schema and tables with different datatypes into same table

I have 2 table. Table A Payment Table B Phone

I want combine result of below 2 query of difference schema into 1 table.

select Payment_DT  
from DW.Payment

SELECT PHONE_NUMBER 
FROM STG_ANALYSIS.PHONE

This is the output I am looking for.

Payment_Dt  Phone_Number
3/31/2018   123-456-7890

Upvotes: 1

Views: 262

Answers (2)

Miracle
Miracle

Reputation: 387

DB Links are pretty much the name of the game here. If you can't get one created on your own, then check if there are any public DB links that you could use.

It's also possible that your DBAs will be willing to have one of their DB Links used to create a materialized view of S2.Table2 on the S1 instance.

Another option might be web services, but my guess is you'd run into much more administrative issues there than you would with a simple DB link. Consider those only if there are good reasons for no links (example: two separate organizations that don't want to open firewall holes between their databases).

Failing those, you're getting into really ugly territory but you might be able to make something work. For example:

Open up both from a tool that can read from multiple connections at once and do the join there. Access. Toad for Data Analysis, whatever.
Use a tool like Toad to copy S2.Table2 to your own schema ("create in another schema" followed by "copy data to another schema")
If you have, or can get, complementary directory objects defined on both servers, create a Materialized View of S2 as an external table in a directory which can be written from S2 and read from S1.

You really don't want to maintain any of these solutions over the long term, though.

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

not knowing the logic but to merge the two should be

select Payment_DT,
      (SELECT PHONE_NUMBER 
      FROM STG_ANALYSIS.PHONE) phone_number  
from DW.Payment

if there is a logic to join the two tables then use inner/left join

Upvotes: 1

Related Questions