• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8396
  • Last Modified:

Using SEQUENCE in an INSERT SELECT

I have 2 tables: an old_table that only contains 'Description' (not all description's are distinct)
       a new_table with 2 columns; pk_ID and Descr
             
I'm going to populate new_table with 'Description' using an insert select. I wanted my pk_ID to
automatically generate a sequential number for each unique description i'm inserting.

So I created a SEQUENCE (next_seq) to handle this, but am not sure the following can be done according to what i've read online.


CREATE SEQUENCE next_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

INSERT INTO new_table (pk_ID, Descr)      
SELECT  DISTINCT next_seq.nextval, Description
FROM old_table;
            
Is the above sql correct ?      If not then how do i generate the next sequential number for each distinct record i'm inserting into new_table?
0
badtz7229
Asked:
badtz7229
  • 9
  • 5
  • 3
  • +1
2 Solutions
 
sventhanCommented:
The SQL looks Great!

Do you have any problem executing it?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that is correct way.
0
 
Jinesh KamdarCommented:
Yes, the SQL is correct.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sventhanCommented:
or you could try this method...
create table new_table  as SELECT  DISTINCT next_seq.nextval, Description
FROM old_table;
0
 
badtz7229Author Commented:
i haven't run the sql to see if it works or not yet.
i'm asking whether it would work because i read online that SEQUENCE will not work in an inner query or using SELECT DISTINCT; which is what i'm doing.
but if you guys say it'll work , then i'll go with that
(btw - i haven't run it yet because this is part of a bigger script so i haven't completed that yet :-) )
0
 
Jinesh KamdarCommented:
Wait, you're right. You can't use DISTINCT with a sequence and vice-versa.

ORA-02287: sequence number not allowed here

Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate here in the statement.

Action: Remove the sequence number.
0
 
Jinesh KamdarCommented:
Try this -

INSERT INTO new_table (Descr, pk_ID)      
SELECT  DISTINCT Description, (SELECT next_seq.nextval FROM dual) FROM old_table;
0
 
sventhanCommented:
I agree with Jinesh.
0
 
Jinesh KamdarCommented:
Scratch that, even that doesn't work !! Working on an alternative ...
0
 
sventhanCommented:
try this works ...

INSERT INTO new_table (pk_ID, Descr)      
SELECT  next_seq.nextval, old_desc.Description
FROM (SELECT  DISTINCT Description from old_table) old_desc
0
 
Jinesh KamdarCommented:
Yes, sventhan's last query works!
0
 
sventhanCommented:
@badtz7229: if that works please do not forget to spilt the points.
0
 
Jinesh KamdarCommented:
Thx sventhan. I must say that this was one of the most over-looked (though not at all illogical) concepts of Oracle, for me at least!
0
 
badtz7229Author Commented:
sventhan AN D jinesh_kamdar thanks a lot. that last sql totally worked perfecty.

btw - i just noticed something weird, don't know if you guys would know. but after inserting about 1412 records. when i called  the .nextVal just to see what would be the next #  would be ; it was .1420. why would that be, given that only 1412 records were created ?

 
0
 
Jinesh KamdarCommented:
Was the new_table empty before this INSERT? If yes, what was the 1st SEQUENCE value INSERTed?
SELECT MIN (pk_id) FROM new_table; ---> Output ??
0
 
badtz7229Author Commented:
new_table was empty because i had just created it using CREATE TABLE...

min (pk_id ) = 1
max(pk_id) = 4712

(sorry - got the #s mixed up, it inserted 4712 but then .nextValue returned 4720

i modified my SEQUENCE object by removing the 'CACHE 20'  line before inserting the records.
CREATE SEQUENCE next_seq
    MINVALUE 1
    MAXVALUE 999999999
    START WITH 1
    INCREMENT BY 1;


0
 
Jinesh KamdarCommented:
How many total rows are there in old_table ?
0
 
Jinesh KamdarCommented:
Check this out.
SQL> CREATE SEQUENCE next_seq
  2      MINVALUE 1
  3      MAXVALUE 999999999
  4      START WITH 1
  5      INCREMENT BY 1;
 
Sequence created.
 
SQL> SELECT * FROM old_table;
 
COL
--------------------------------------------------------------------------------
ABC
ABC
PQR
 
SQL> CREATE TABLE new_table(pk_id NUMBER PRIMARY KEY, descr VARCHAR2(10));
 
Table created.
 
SQL> INSERT INTO new_table SELECT next_seq.NEXTVAL, col FROM (SELECT DISTINCT col FROM old_table);
 
2 rows created.
 
SQL> SELECT next_seq.NEXTVAL FROM dual;
 
   NEXTVAL
----------
         3
 
SQL> DROP SEQUENCE next_seq;
 
Sequence dropped.
 
SQL> CREATE SEQUENCE next_seq
  2      MINVALUE 1
  3      MAXVALUE 999999999
  4      START WITH 1
  5      INCREMENT BY 1;
 
Sequence created.
 
SQL> TRUNCATE TABLE new_table;
 
Table truncated.
 
SQL> ALTER TABLE new_table DROP COLUMN descr;
 
Table altered.
 
SQL> INSERT INTO new_table SELECT next_seq.NEXTVAL FROM dual CONNECT BY level <= 4712;
 
4712 rows created.
 
SQL> SELECT next_seq.NEXTVAL FROM dual;
 
   NEXTVAL
----------
      4713
 
SQL> 

Open in new window

0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 9
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now