Home » SQL & PL/SQL » SQL & PL/SQL » migrating data from different tables into a single table (sql*plus 10.2.0, winXP sp3)
migrating data from different tables into a single table [message #348366] Tue, 16 September 2008 09:30 Go to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

hi guys,
i m just trying to migrate data from more than one table into a single table. i have tried to use MERGE INTO command to do that task but it doesn't work or i havn't done it correctly.

i tried the following syntax to populate data from other tables into the targets_information table.

1 MERGE INTO
2 TARGETS_INFORMATION ti
3 USING
4 TARGETS t,
5 LOCATIONS l,
6 INFORMATION i
7 ON
8 (
9 ti.target_id=t.target_id
10 and
11 ti.information_id=i.information_id
12 )
13 WHEN MATCHED THEN
14 UPDATE
15 SET
16 ti.target_id=t.target_id,
17 ti.information_id=i.information_id
18 WHEN NOT MATCHED THEN
19 INSERT
20 (
21 ti.target_name=t.first_name
22 ti.target_location_description=l.description
23 )
24 VALUES
25 (
26 t.first_name,
27 l.description
28 )
29 ;


thanks
Re: migrating data from different tables into a single table [message #348369 is a reply to message #348366] Tue, 16 September 2008 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


My car does not work.
Please tell me how to make my car go.
Re: migrating data from different tables into a single table [message #348372 is a reply to message #348366] Tue, 16 September 2008 09:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It would have been helpful if you could have posted the error you got.

Looking at the code, your mistake is that you cannot refer to the columns used in the ON clause in the WHEN MATCHED clause.

ie your code:
 WHEN MATCHED THEN
UPDATE
SET
ti.target_id=t.target_id,
ti.information_id=i.information_id
should be
 WHEN MATCHED THEN UPDATE
SET ti.information_id=i.information_id


If you think about it, you already know those values match - there's no need to set them equal to each other.
Re: migrating data from different tables into a single table [message #348384 is a reply to message #348366] Tue, 16 September 2008 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 16 September 2008 07:48
No one can help without relevant information like table description, for instance.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Re: migrating data from different tables into a single table [message #348389 is a reply to message #348366] Tue, 16 September 2008 10:47 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

i am afraid migrating data from different tables is not possible at a time !
Re: migrating data from different tables into a single table [message #348390 is a reply to message #348389] Tue, 16 September 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is possible.

Regards
Michel
Re: migrating data from different tables into a single table [message #348441 is a reply to message #348366] Tue, 16 September 2008 15:24 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

i have created a new "targets_info" table and the table doesn't contain any data. so i need to migrate the data from other tables and the data should be collected from "targets", "information" and "location" tables.

the new table has got the field names as
target_id
target_location_description
information_id

and i tried many ways but nothing i can find appropriate to do.

the earlier stated one is the type of syntax i have tried.

if anyone can give me any idea that i can merge data from different tables at a time by showing some sytax would be very much appreciated.

thanks.
Re: migrating data from different tables into a single table [message #348483 is a reply to message #348441] Tue, 16 September 2008 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As sonn as you will follow the rules/guidelines.

Regards
Michel
Re: migrating data from different tables into a single table [message #351878 is a reply to message #348441] Fri, 03 October 2008 10:03 Go to previous message
rwfereira
Messages: 1
Registered: October 2008
Location: london
Junior Member
hi ukdas,
im having the same problem as you with the data merging in the target_info table.
can you please help me if you have any soultion to this??

cheers..
Previous Topic: Procedure executed from where??????
Next Topic: While printing refcursor " ORA-01858 " is coming.
Goto Forum:
  


Current Time: Sat Dec 03 09:55:34 CST 2016

Total time taken to generate the page: 0.04415 seconds