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?
badtz7229Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.