Home » SQL & PL/SQL » SQL & PL/SQL » insert data into table
insert data into table [message #194676] Sat, 23 September 2006 22:58 Go to next message
rajuakula
Messages: 63
Registered: March 2005
Member
hi,

I have 4 tables and need to join two tables(table1 and table2) and pull the data and insert into table3 and table4.

table1

id fname lname
1 abc mm
2 xyz nn
3 pqr dd
4 mno cc

table 2

fname lname changedate val

abc mm 10-aug-2006 10:22:33 AM 20
abc mm 10-aug-2006 10:28:33 AM 20
abc mm 10-aug-2006 10:29:33 PM 22
xyz nn 23-Jul-2006 02:22:15 AM 15
xyz nn 24-Jul-2006 01:22:27 AM 20
pqr dd 13-Jan-2006 07:12:26 AM 45
pqr dd 13-Jan-2006 12:02:23 AM 30


now I want to insert the data in table3 as below
(i.e. I need to join table 1 and table 2 and insert into table 3 as below)

table3

id changedate val
1 10-aug-2006 10:29:33 AM 22
2 24-Jul-2006 01:22:27 AM 20
3 13-Jan-2006 12:02:23 AM 30

In table4 I want as below
I want to insert the record by joining table1 and table2 and insert only the id fieldname(i.e. changedate,val) and the value. If the value is changed then only insert that value.

table4

id fieldname fieldval
-------------------------------
1 changedate 10-aug-2006 10:22:33 AM
1 val 20
1 changedate 10-aug-2006 10:28:33 AM
1 changedate 10-aug-2006 10:29:33 PM
1 val 22
2 changedate 23-Jul-2006 02:22:15 AM
2 val 15
2 changedate 24-Jul-2006 01:22:27 AM
2 val 20
3 changedate 13-Jan-2006 07:12:26 AM
3 val 45
3 changedate 13-Jan-2006 12:02:23 AM
3 val 30
Re: insert data into table [message #194677 is a reply to message #194676] Sat, 23 September 2006 23:00 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Congratulations. Of course you realize that you can only INSERT INTO a single table in a single SQL statement.
So let us see what you've tried so far & explain why they do not satisfy the requirements.
Re: insert data into table [message #194680 is a reply to message #194677] Sat, 23 September 2006 23:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is the idea of your table4? All the data in there is already stored in table3.
Re: insert data into table [message #194681 is a reply to message #194677] Sat, 23 September 2006 23:43 Go to previous messageGo to next message
rajuakula
Messages: 63
Registered: March 2005
Member
I want to insert data into two tables as separate queries
not as single query.

insert into table3(select a.id,b.changedate,b.val from
table1 a,table2 b where .......)

Here I would like to know the where condition which should isnert the data in table 3 as the data I sent.


same with table4 also.


thanks
Re: insert data into table [message #194682 is a reply to message #194676] Sat, 23 September 2006 23:49 Go to previous messageGo to next message
rajuakula
Messages: 63
Registered: March 2005
Member
insert into table3(select a.id,b.changedate,b.val from
table1 a,table2 b where upper(a.fname) = upper(b.fname) and upper(a.last_name) = upper(b.last_name) and b.changedate in
(select cast(max(b.changedate) as timestamp) from table2 b,table1 a where upper(a.fname) = upper(b.fname) and upper(a.last_name) = upper(b.last_name) group by a.id ) )

this way I tried but according to my requirement it should insert 3 records but it is inserting all the record I mean 7 records.How should I do this and also in the next table4
I am not able get any idea.

it is urgent

thanks
Re: insert data into table [message #194685 is a reply to message #194676] Sun, 24 September 2006 00:33 Go to previous messageGo to next message
rajuakula
Messages: 63
Registered: March 2005
Member
Is there a way to write astored proc and insert records into table4 according to my requirement,if so plz. send me the stored proc.

thanks in advance
Re: insert data into table [message #195278 is a reply to message #194682] Wed, 27 September 2006 14:12 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I demonstrated two methods below. The second one is the more modern, more efficient.


SCOTT@10gXE> SELECT * FROM table1
  2  /

        ID FNAME LNAME
---------- ----- -----
         1 abc   mm
         2 xyz   nn
         3 pqr   dd
         4 mno   cc

SCOTT@10gXE> SELECT * FROM table2
  2  /

FNAME LNAME CHANGEDATE                     VAL
----- ----- ----------------------- ----------
abc   mm    10-aug-2006 10:22:33 AM         20
abc   mm    10-aug-2006 10:28:33 AM         20
abc   mm    10-aug-2006 10:29:33 AM         22
xyz   nn    23-jul-2006 02:22:15 AM         15
xyz   nn    24-jul-2006 01:22:27 AM         20
pqr   dd    13-jan-2006 07:12:26 AM         45
pqr   dd    13-jan-2006 12:02:23 AM         30

7 rows selected.

SCOTT@10gXE> INSERT INTO table3 (id, changedate, val)
  2  SELECT table1.id, table2.changedate, table2.val
  3  FROM   table1, table2
  4  WHERE  table1.fname = table2.fname
  5  AND    table1.lname = table2.lname
  6  AND    (table1.id, table2.changedate) IN
  7  	    (SELECT table1.id, MAX (table2.changedate)
  8  	     FROM   table1, table2
  9  	     WHERE  table1.fname = table2.fname
 10  	     AND    table1.lname = table2.lname
 11  	     GROUP  BY table1.id)
 12  /

3 rows created.

SCOTT@10gXE> SELECT * FROM table3
  2  /

        ID CHANGEDATE                     VAL
---------- ----------------------- ----------
         1 10-aug-2006 10:29:33 AM         22
         2 24-jul-2006 01:22:27 AM         20
         3 13-jan-2006 07:12:26 AM         45

SCOTT@10gXE> TRUNCATE TABLE table3
  2  /

Table truncated.

SCOTT@10gXE> INSERT INTO table3 (id, changedate, val)
  2  SELECT table1.id,
  3  	    MAX (table2.changedate) KEEP
  4  	      (DENSE_RANK LAST ORDER BY table2.changedate),
  5  	    MAX (table2.val) KEEP
  6  	      (DENSE_RANK LAST	ORDER BY table2.changedate)
  7  FROM   table1, table2
  8  WHERE  table1.fname = table2.fname
  9  AND    table1.lname = table2.lname
 10  GROUP  BY table1.id
 11  /

3 rows created.

SCOTT@10gXE> SELECT * FROM table3
  2  /

        ID CHANGEDATE                     VAL
---------- ----------------------- ----------
         1 10-aug-2006 10:29:33 AM         22
         2 24-jul-2006 01:22:27 AM         20
         3 13-jan-2006 07:12:26 AM         45

SCOTT@10gXE> 



Previous Topic: Getting error
Next Topic: Fine Grain Access Control
Goto Forum:
  


Current Time: Sun Dec 04 08:23:52 CST 2016

Total time taken to generate the page: 0.10996 seconds