Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure to merge Two tables
Stored Procedure to merge Two tables [message #261250] Wed, 22 August 2007 03:55 Go to next message
oracleram
Messages: 23
Registered: November 2006
Junior Member
Hi Every1,
i need a stored procedure to merge the data of two different tables,for example
Table1 has the values Deptno
1
2
3
Table2 has the values Deptno1
1
2
3
4

the required result will be

1
2
3
4
that is we require common fields of two tables,as well as any additional fields of table2,thank u for helping me...


Thanks & Regards,

Ram Nainar S
Re: Stored Procedure to merge Two tables [message #261255 is a reply to message #261250] Wed, 22 August 2007 04:03 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
There could be two methods:

1. Merge: Use merge command This is very fats as well.Please check the exact syntax of merge command.This is better when you want to have the two tables in sync.This will update one of the table.So it wont be good fro you if you just want to check all the possible values of dept in both tables. This would be expensive as compared to outer join as this will use insert and update of the table.

2. Outer Join: If you need result set then you could make use of outer join.This would be fatest method.This is recommended.

3. Union : This could be little bit expensive method.If table size is quite samll then it will have same cost as outer join.
select deptno from table1
union
select deptno1 from table2
Re: Stored Procedure to merge Two tables [message #261263 is a reply to message #261250] Wed, 22 August 2007 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use union.
Doesn't it what you want: all the distinct values from both tables?
Or is this: all the common value from both tables plus the values of table2 that are not in table 1 but not the values of table 1 that are not in table 2?

Regards
Michel
Re: Stored Procedure to merge Two tables [message #261269 is a reply to message #261263] Wed, 22 August 2007 04:25 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Hi Michel

I knew that but i User has said that he wants to merge the data of two tables.Thats why i have specfied that.

if that is the case then only merge and outer join would be only options.
Re: Stored Procedure to merge Two tables [message #261284 is a reply to message #261269] Wed, 22 August 2007 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
darshanmeel, I was not answering to you but to OP.
Btw, this is not because OP said "merge" that he want to merge in the database, he maybe just wants to merge the results in the output.

Regards
Michel
Re: Stored Procedure to merge Two tables [message #261295 is a reply to message #261284] Wed, 22 August 2007 05:04 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
I was talking about "Merge" in output only not in database.

Merging of output means taking all values which are common to both tables as well as those values from each table which are not present in other table.
Re: Stored Procedure to merge Two tables [message #261336 is a reply to message #261295] Wed, 22 August 2007 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post an example using "merge" to merge the ouput of 2 tables.
I should be very curious to see that.
For what I know, "merge" statement does not output anything.

Regards
Michel
Re: Stored Procedure to merge Two tables [message #261339 is a reply to message #261336] Wed, 22 August 2007 06:12 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
I told you earlier that i cant use the sql plus.So I cant show you an example.

I have mentioned merging of two tables.i.e. merging two tables in a single table not output.

I will reply to you when i will be free from my vritual machine.

I will post you the whole example.Till then please wait.

Please no more offence.
Re: Stored Procedure to merge Two tables [message #261341 is a reply to message #261339] Wed, 22 August 2007 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I was talking about "Merge" in output only not in database.

Quote:
I have mentioned merging of two tables.i.e. merging two tables in a single table not output

No inconsistencies between that?
Choose your side.

Regards
Michel

Re: Stored Procedure to merge Two tables [message #261540 is a reply to message #261250] Thu, 23 August 2007 00:13 Go to previous message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Please use the following query.Left outer Join is best.

select t2.deptno1 
from t2 left outer join t1
on t2.depntno1=t1.deptno;


If you want to use all the values from both the tables.Then use full outer join.
Previous Topic: To find the number of occurences of a string in a clob
Next Topic: showing the values just once for matching records
Goto Forum:
  


Current Time: Sun Dec 04 14:59:59 CST 2016

Total time taken to generate the page: 0.10841 seconds