Multiple table insert involving sequences

Dev Shed Forums Sponsor:

February 4th, 2013, 02:45 PM

fee1975

Contributing User

Join Date: Feb 2008

Posts: 76

Time spent in forums: 1 Day 1 h 42 m 42 sec
Reputation Power: 6

Multiple table insert involving sequences

Hi, I have a temp table and want to insert that data into 2 tables. In both of my tables i have a sequence column but in my second table its a foreign key to the main table

PHP Code:


		
			
 $up_query="INSERT INTO AFFILIATE (FNAME,LNAME,EMAIL,PHONE,ORG,ADDRESS1,ADDRESS2,COUNTY_ID,AFF_ID)

                Select a.FNAME, a.LNAME,a.EMAIL,a.PHONE, a.ORG, a.ADDRESS1, a.ADDRESS2,C.COUNTY_ID,'AF'||aff_seq.nextval 

                FROM temp_aff A LEFT OUTER JOIN COUNTY C

                ON UPPER(A.COUNTY)=C.CNAME";

$up_query2=    "INSERT INTO TEST4 (DEPT_ID,CAMP_ID,AFF_ID, PASSWD) 

                SELECT D.DEPT_ID,C.CAMP_ID,aff_seq.currval,'Zq'||dbms_random.string('A',4)||'$8' from dual,TEMP_AFF A 

                left outer join DEPT D ON (UPPER(A.DEPT)=UPPER(D.DNAME))

                left outer join CAMPUS C ON (UPPER(A.CAMPUS)=UPPER(C.CPNAME))";

However in my test4 table its inserting the aff_seq.currval for all records. Its not incrementing as in the affiliate table.
I appreciate any suggestions which would help me resolve it

February 8th, 2013, 05:02 AM

dariyoosh

Contributing User

Join Date: Nov 2012

Location: Iran / France

Posts: 132

Time spent in forums: 2 Days 6 h 13 m 3 sec
Reputation Power: 133

Hello there,

The CURRVAL pseudocolumn returns the current value of the sequence without incrementing/updating the sequence, and that corresponds to the value returned by the sequence when the NEXTVAL was queried for the last time upon that sequence. As a result if you call 100 times CURRVAL, 100 times you get the same value. Here is an example:

Code:

SQL> CREATE SEQUENCE my_test_seq;

Sequence created.

SQL> SET SERVEROUTPUT ON;
SQL> 
SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.NEXTVAL);
  3  END;
  4  /
The next value of the sequence is: 1

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
  3      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
  4      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
  5      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
  6      DBMS_OUTPUT.PUT_LINE('The next value of the sequence is: ' || my_test_seq.CURRVAL);
  7  END;
  8  /
The next value of the sequence is: 1
The next value of the sequence is: 1
The next value of the sequence is: 1
The next value of the sequence is: 1
The next value of the sequence is: 1

PL/SQL procedure successfully completed.

Now what I understood from your problem is that the sequence value in the TEST4 table is in fact a foreign key referencing the aff_id (thus a unique index I believe) column in the table temp_aff. What you can do is insert the values as you do now and then do an update of the sequence values accordingly. Something like this

Code:

UPDATE test4 t1
SET aff_id = (SELECT aff_id
              FROM temp_aff t2
              WHERE <...>
             )

And in the WHERE clause you need to write a condition that links/maps uniquely each row of the table temp_aff to its corresponding line in the table test4. Not having the DDL of your tables I cannot tell you more about that, but just by looking at the column names, I'm not sure whether currently you can do that. Probably you may need to normalize your tables.

Regards,
Dariyoosh

Multiple table insert involving sequences

Developer Shed Advertisers and Affiliates