Durga
Durga

Reputation: 565

How to retrieve data from two tables with same column names in oracle

I have two tables one working table another one is backup table. Both tables have same columns. Now I want few columns data from two tables in single query.

It is possible in single query?

Table name : transationdetails workingtable transationdetails_backup is backup table

For Example I write individual queries here:

select txnid as txnid
     , date as transactiondate
     , amount as amount 
  from transationdetails;

select txnid as txnid
     , date as transactiondate
     , amount as amount 
  from transationdetails_backup;

Expecting Result:

select txnid as txnid
     , date as transactiondate
     , amount as amount 
  from transationdetails and transationdetails_backup;

Upvotes: 0

Views: 3222

Answers (3)

Nikhil
Nikhil

Reputation: 3950

this will work:

select txnid as txnid, date as transactiondate, amount as amount from transationdetails union all select txnid as txnid, date as transactiondate, amount as amount from transationdetails_backup;

Upvotes: 1

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

Try this code:

SELECT * FROM transationdetails 
UNION ALL
SELECT * FROM transationdetails_backup;

I believe simple UNION ALL would satisfy your needs.

Upvotes: 1

rudy tjhia
rudy tjhia

Reputation: 20

You can use Concat function same as in MySQL , the easiest way is just put

|| (Double Pipe , between column that you want to join)

EX :
select a.id , (a.id||a.name||b.job||a.stats) as backupDetail 
from "FirstTable" as a
inner join "SecondTable" as b on b.id = a.id 
Where a.id="YourParameter";

Upvotes: 0

Related Questions