Home » SQL & PL/SQL » SQL & PL/SQL » Select data from 2 tables and insert into another table
Select data from 2 tables and insert into another table [message #583085] Thu, 25 April 2013 11:06 Go to next message
DianaPersaud
Messages: 3
Registered: April 2013
Junior Member
Guys,

I want to select data from different tables and insert this into one table based on some conditions:

SELECT *
FROM   welltest_msr
WHERE  well_s  = 3419740
AND    check_ind = 1
ORDER BY msr_date DESC
;


WELL_S   MSR_DATE
3419740	23-OCT-12
3419740	18-SEP-12
3419740	28-AUG-12
3419740	16-JUL-12
3419740	14-JUN-12
3419740	11-MAY-12
3419740	12-APR-12
3419740	15-MAR-12
3419740	16-FEB-12
3419740	23-JAN-12
3419740	05-JAN-12
3419740	07-DEC-11


select m.pfnu_s
,       m.msr_date
,      p.value
from   msr_act m
,        msr_act_par p
,       r_msr_act_par r
where  m.msr_act_s       = p.msr_act_s
and    p.r_msr_act_par_s = r.r_msr_act_par_s
and    r.abbr            = 'FBH1'
and    m.pfnu_s             = 3419740
and    p.check_ind         = 1
ORDER BY m.msr_date DESC
;

PFNU_S   MSR_DATE       VALUE
3419740	23-OCT-12        445
3419740	19-SEP-12	 447
3419740	28-AUG-12	 441
3419740	16-JUL-12	 449
3419740	14-JUN-12	 449
3419740	09-MAY-12	 451
3419740	11-APR-12	 447
3419740	12-DEC-11	 434
3419740	01-DEC-11	 426



So I tried doing this with selecting the data and looping through it to do the insert.

DECLARE
 
--
--  WELLS
--
CURSOR c_well
IS
SELECT *
FROM   well
where well_s = 3419740
;
--
-- Welltest
--
CURSOR c_welltest ( v_well_s NUMBER )
IS
SELECT *
FROM   welltest_msr
WHERE  well_s  = 3419740
AND    check_ind = 1
ORDER BY msr_date DESC
;
 
--------
--------
--------
 
-- FBHP
--
 
CURSOR c_fbhp_dsr ( v_well_s NUMBER )
IS
select m.pfnu_s
,       m.msr_date
,      p.value
from   msr_act m
,        msr_act_par p
,       r_msr_act_par r
where  m.msr_act_s       = p.msr_act_s
and    p.r_msr_act_par_s = r.r_msr_act_par_s
and    r.abbr            = 'FBH1'
and    m.pfnu_s             = 3419740
and    p.check_ind         = 1
ORDER BY m.msr_date DESC
;
r_fbhp_dsr            c_fbhp_dsr%ROWTYPE;
r_fbhp_dsr_leeg       c_fbhp_dsr%ROWTYPE;

BEGIN

FOR r_well IN c_well
LOOP
 
--
 r_fbhp_dsr         := r_fbhp_dsr_leeg;
   OPEN  c_fbhp_dsr ( r_well.well_s  );
   FETCH c_fbhp_dsr INTO r_fbhp_dsr;
--
--
--
--
--
 
 FOR r_welltest IN c_welltest ( r_well.well_s )
   LOOP
 
--
--
--
--
WHILE NVL(r_fbhp_dsr.msr_date,r_welltest.msr_date - 1) >= 
r_welltest.msr_date
     LOOP
       FETCH c_fbhp_dsr INTO r_fbhp_dsr;
     -- dbms_output.put_line (r_fbhp_dsr.msr_date);
       IF    c_fbhp_dsr%NOTFOUND
       THEN
          BEGIN
             r_fbhp_dsr := r_fbhp_dsr_leeg;
             EXIT;
          END;
       END IF;
     END LOOP;
--

INSERT INTO SO_WELLTEST_PLUS
       (    WELL_S
       ,    WELLNAME
       ,    WELLTEST_DATE
       ,    FBHP_SRO
       ,    FBHP_SRO_DATE
      
VALUES
        (      r_well.well_s
         ,     r_well.wellname
         ,     r_welltest.msr_date
         ,      r_fbhp_dsr.value
         ,    r_fbhp.msr_date
        );
END LOOP;

CLOSE c_fbhp_dsr;
 
COMMIT;
END LOOP;

END;



The output is like this:

well_s        wellname           welltest_date  fbhp_sro            fbhp_sro_date
3419740	       3Z22	          23-OCT-12	447	            19-SEP-12
3419740	       3Z22	          18-SEP-12	441	            28-AUG-12
3419740	       3Z22	          28-AUG-12	449	            16-JUL-12
3419740	       3Z22	          16-JUL-12	449	            14-JUN-12
3419740	       3Z22	          14-JUN-12	451	            09-MAY-12
3419740	       3Z22	          11-MAY-12	451	            09-MAY-12
3419740	       3Z22	          12-APR-12	447	            11-APR-12
3419740	       3Z22	          15-MAR-12	434	            12-DEC-11
3419740	       3Z22	          16-FEB-12	434	            12-DEC-11
3419740	       3Z22	          23-JAN-12	434	            12-DEC-11
3419740	       3Z22	          05-JAN-12	434	            12-DEC-11
3419740	       3Z22	          07-DEC-11	426	            01-DEC-11


But what I want is this:
When the the dates are equal, the fbhp date should be inserted for the same welltest_date, otherwise a fbhp_date smaller than welltest should be inserted.
Can this be done, if yes, how can I do this?
well_s        wellname          welltest_date  fbhp_sro            fbhp_sro_date
3419740	       3Z22	          23-OCT-12	445	            23-OCT-12
3419740	       3Z22	          18-SEP-12	441	            28-AUG-12
3419740	       3Z22	          28-AUG-12	441	            28-AUG-12
3419740	       3Z22	          16-JUL-12	449	            16-JUL-12
3419740	       3Z22	          14-JUN-12	449	            14-JUN-12
3419740	       3Z22	          11-MAY-12	451	            09-MAY-12
3419740	       3Z22	          12-APR-12	447	            11-APR-12
3419740	       3Z22	          15-MAR-12	434	            12-DEC-11
3419740	       3Z22	          16-FEB-12	434	            12-DEC-11
3419740	       3Z22	          23-JAN-12	434	            12-DEC-11
3419740	       3Z22	          05-JAN-12	434	            12-DEC-11
3419740	       3Z22	          07-DEC-11	426	            01-DEC-11


Thanks,
Diana
Re: Select data from 2 tables and insert into another table [message #583089 is a reply to message #583085] Thu, 25 April 2013 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Select data from 2 tables and insert into another table [message #583091 is a reply to message #583085] Thu, 25 April 2013 12:45 Go to previous messageGo to next message
joy_division
Messages: 4512
Registered: February 2005
Location: East Coast USA
Senior Member
DianaPersaud wrote on Thu, 25 April 2013 12:06

well_s        wellname      welltest_date  fbhp_sro            fbhp_sro_date
3419740	       3Z22	     23-OCT-12	     447	         19-SEP-12


But what I want is this:
When the the dates are equal, the fbhp date should be inserted for the same welltest_date, otherwise a fbhp_date smaller than welltest should be inserted.
well_s        wellname     welltest_date  fbhp_sro            fbhp_sro_date
3419740	       3Z22	     23-OCT-12	    445	                23-OCT-12



Makes no sense to me.

[Updated on: Thu, 25 April 2013 12:46]

Report message to a moderator

Re: Select data from 2 tables and insert into another table [message #583181 is a reply to message #583089] Fri, 26 April 2013 09:15 Go to previous messageGo to next message
DianaPersaud
Messages: 3
Registered: April 2013
Junior Member
Here is the test case:


Create table well
( well_s not null number(8)
, wellname not null varchar2(30)
);

Create table welltest_msr
(well_s  not null number (8)
,msr_date date
,check_ind not null number(1)
);

Create table msr_act
(msr_act_s not  null number(8)
, pfnu_s not null number(8)
,msr_date date
);

Create table msr_act_par
( MSR_ACT_S                   NUMBER(8) 
, R_MSR_ACT_PAR_S             NUMBER(8) 
, VALUE                       NUMBER 
, CHECK_IND          NOT NULL NUMBER(1) 
);

Create table r_msr_act_par

( R_MSR_ACT_PAR_S NOT NULL NUMBER(8)
,ABBR            NOT NULL VARCHAR2(40) 
);


Create table so_welltest_plus
(well_s number (14,0)
, wellname varchar2(24)
, welltest_date date
,fbhp_sro number
, fbhp_sro_date date
)




insert into well
(well_s, wellname)
values
( 3419740,'3Z22');


insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740, '23-OCT-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'18-SEP-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'28-AUG-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'16-JUL-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'14-JUN-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'11-MAY-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740, '12-APR-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'15-MAR-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'16-FEB-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'23-JAN-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740, '05-JAN-12', 1);

insert into welltest_msr
(well_s, msr_date, check_ind)
values
( 3419740,'07-DEC-11', 1);


insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
( 3656218,3419740, '23-OCT-12');


insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
( 3633726,3419740, '19-SEP-12');

insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
( 3615485,3419740, '28-AUG-12');

insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
( 3593928,3419740, '16-JUL-12');

insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
( 3573966,3419740, '14-JUN-12');

insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
(3550860, 3419740, '09-MAY-12');

insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
( 3527628,3419740, '11-APR-12');

insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
( 3454608,3419740, '12-DEC-11');

insert into msr_act
(msr_act_s, pfnu_s, msr_date)
values
( 3449290,3419740, '01-DEC-11');


insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3449290,9105196 , 426, 1);

insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3454608,9105196 , 434 , 1);

insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3527628,9105196 , 447, 1);

insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3550860,9105196 , 451, 1);

insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3573966,9105196 , 449, 1);

insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3593928,9105196 , 449, 1);

insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3615485,9105196 , 441, 1);

insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3633726,9105196 , 447, 1);

insert into msr_act_par
(msr_act_s, R_MSR_ACT_PAR_S  , VALUE, CHECK_IND )
values
( 3656218,9105196 , 445, 1);

insert into r_msr_act_par
(R_MSR_ACT_PAR_S, ABBR )
values
( 9105196,'FBH1');

Commit;



So here is what I want to do:
Compare the msr_date from query1 with the msr_date of query2 for the different values of WELL_S.

When there are equal dates, the values from query2 should be inserted into table SO_WELLTEST_PLUS table otherwise, values for a query1.msr_date > query2.msr_date should be inserted.

Query1:
SELECT *
FROM   welltest_msr
WHERE  well_s  = 3419740
AND    check_ind = 1
ORDER BY msr_date DESC
;


Query2:

select m.pfnu_s
,       m.msr_date
,      p.value
from   msr_act m
,        msr_act_par p
,       r_msr_act_par r
where  m.msr_act_s       = p.msr_act_s
and    p.r_msr_act_par_s = r.r_msr_act_par_s
and    r.abbr            = 'FBH1'
and    m.pfnu_s             = 3419740
and    p.check_ind         = 1
ORDER BY m.msr_date DESC
;




Desired Output:


well_s        wellname          welltest_date  fbhp_sro            fbhp_sro_date
3419740	       3Z22	          23-OCT-12	445	            23-OCT-12
3419740	       3Z22	          18-SEP-12	441	            28-AUG-12
3419740	       3Z22	          28-AUG-12	441	            28-AUG-12
3419740	       3Z22	          16-JUL-12	449	            16-JUL-12
3419740	       3Z22	          14-JUN-12	449	            14-JUN-12
3419740	       3Z22	          11-MAY-12	451	            09-MAY-12
3419740	       3Z22	          12-APR-12	447	            11-APR-12
3419740	       3Z22	          15-MAR-12	434	            12-DEC-11
3419740	       3Z22	          16-FEB-12	434	            12-DEC-11
3419740	       3Z22	          23-JAN-12	434	            12-DEC-11
3419740	       3Z22	          05-JAN-12	434	            12-DEC-11
3419740	       3Z22	          07-DEC-11	426	            01-DEC-11


I hope it's clear now.

Thanks,
Diana
Re: Select data from 2 tables and insert into another table [message #583195 is a reply to message #583181] Fri, 26 April 2013 23:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
The most efficient method would be to just use one sql insert statement, instead of using pl/sql and cursors and looping. Please see the demonstration below.

-- starting data:
SCOTT@orcl_11gR2> select * from well
  2  /

    WELL_S WELLNAME
---------- --------
   3419740 3Z22

1 row selected.

SCOTT@orcl_11gR2> select * from welltest_msr
  2  /

    WELL_S MSR_DATE   CHECK_IND
---------- --------- ----------
   3419740 23-OCT-12          1
   3419740 18-SEP-12          1
   3419740 28-AUG-12          1
   3419740 16-JUL-12          1
   3419740 14-JUN-12          1
   3419740 11-MAY-12          1
   3419740 12-APR-12          1
   3419740 15-MAR-12          1
   3419740 16-FEB-12          1
   3419740 23-JAN-12          1
   3419740 05-JAN-12          1
   3419740 07-DEC-11          1

12 rows selected.

SCOTT@orcl_11gR2> select * from msr_act
  2  /

 MSR_ACT_S     PFNU_S MSR_DATE
---------- ---------- ---------
   3656218    3419740 23-OCT-12
   3633726    3419740 19-SEP-12
   3615485    3419740 28-AUG-12
   3593928    3419740 16-JUL-12
   3573966    3419740 14-JUN-12
   3550860    3419740 09-MAY-12
   3527628    3419740 11-APR-12
   3454608    3419740 12-DEC-11
   3449290    3419740 01-DEC-11

9 rows selected.

SCOTT@orcl_11gR2> select * from msr_act_par
  2  /

 MSR_ACT_S R_MSR_ACT_PAR_S      VALUE  CHECK_IND
---------- --------------- ---------- ----------
   3449290         9105196        426          1
   3454608         9105196        434          1
   3527628         9105196        447          1
   3550860         9105196        451          1
   3573966         9105196        449          1
   3593928         9105196        449          1
   3615485         9105196        441          1
   3633726         9105196        447          1
   3656218         9105196        445          1

9 rows selected.

SCOTT@orcl_11gR2> select * from r_msr_act_par
  2  /

R_MSR_ACT_PAR_S ABBR
--------------- ----------------------------------------
        9105196 FBH1

1 row selected.

SCOTT@orcl_11gR2> select * from so_welltest_plus
  2  /

no rows selected


-- insert:
SCOTT@orcl_11gR2> insert into so_welltest_plus
  2    (well_s, wellname, welltest_date, fbhp_sro, fbhp_sro_date)
  3  select w.well_s, w.wellname, wm.msr_date,
  4  	    max (p.value) keep (dense_rank last order by m.msr_date),
  5  	    max (m.msr_date) fbhp_sro_date
  6  from   well w, welltest_msr wm, msr_act m, msr_act_par p, r_msr_act_par r
  7  where  w.well_s = wm.well_s
  8  and    wm.msr_date >= m.msr_date
  9  and    m.msr_act_s  = p.msr_act_s
 10  and    p.r_msr_act_par_s = r.r_msr_act_par_s
 11  and    w.well_s = 3419740
 12  and    wm.check_ind = 1
 13  and    m.pfnu_s = 3419740
 14  and    p.check_ind = 1
 15  and    r.abbr = 'FBH1'
 16  group  by w.well_s, w.wellname, wm.msr_date
 17  /

12 rows created.


-- results:
SCOTT@orcl_11gR2> select *
  2  from   so_welltest_plus
  3  order  by welltest_date desc, fbhp_sro_date desc
  4  /

    WELL_S WELLNAME WELLTEST_   FBHP_SRO FBHP_SRO_
---------- -------- --------- ---------- ---------
   3419740 3Z22     23-OCT-12        445 23-OCT-12
   3419740 3Z22     18-SEP-12        441 28-AUG-12
   3419740 3Z22     28-AUG-12        441 28-AUG-12
   3419740 3Z22     16-JUL-12        449 16-JUL-12
   3419740 3Z22     14-JUN-12        449 14-JUN-12
   3419740 3Z22     11-MAY-12        451 09-MAY-12
   3419740 3Z22     12-APR-12        447 11-APR-12
   3419740 3Z22     15-MAR-12        434 12-DEC-11
   3419740 3Z22     16-FEB-12        434 12-DEC-11
   3419740 3Z22     23-JAN-12        434 12-DEC-11
   3419740 3Z22     05-JAN-12        434 12-DEC-11
   3419740 3Z22     07-DEC-11        426 01-DEC-11

12 rows selected.

Re: Select data from 2 tables and insert into another table [message #583386 is a reply to message #583195] Tue, 30 April 2013 06:44 Go to previous messageGo to next message
DianaPersaud
Messages: 3
Registered: April 2013
Junior Member
Thank you Barbara,

This will help me a lot.

Diana
Re: Select data from 2 tables and insert into another table [message #583736 is a reply to message #583386] Fri, 03 May 2013 08:00 Go to previous message
pointers
Messages: 350
Registered: May 2008
Senior Member
So if something can be done in sql, that would be the most prefered way -- thinking if it can be done in sql is always a better approach.

Regards,
Pointers
Previous Topic: Please help me to tune these procedures
Next Topic: Need Help with After Trigger
Goto Forum:
  


Current Time: Tue Sep 16 14:36:40 CDT 2014

Total time taken to generate the page: 0.10370 seconds