4

I have some inserts in a development environment to release, the developer has not used sequences to obtain the correct identifiers when doing inserts. What he has done is gone into the production database and found out what the largest identifier is and added a few hundred numbers to it. To boot he has now left the company.

So the script to prod is filled with hundreds of these:

INSERT INTO table (sequencecol, cola, colb) VALUES (90001,'test','test');

INSERT INTO childtableB (foreignkeyTableA, cola) VALUES (90001,'test')

When it should have been

INSERT INTO tableA (sequencecol, cola, colb) VALUES (tablesequence.NEXTVAL,'test','test');
INSERT INTO childtableB (foreignkeyTableA, cola) VALUES (tablesequence.CURRVAL,'test')

The scneario i am dealing with is more complicated than this, and i dont have the time to rewrite, instead i just want to set the sequence to the max value of the records inserted as part of this script.

Is it possible to alter the value of a sequence to set it to the value of the highest value +1 of the records i am to insert?

I'm open to better suggestions

Neophyte.net
  • 561
  • 1
  • 7
  • 14

1 Answers1

11

The approach is good.

But looking at the answers to this related questions, it is not easy to just reset a sequence to a particular value.

Since you don't need to go back here, maybe just write a small script that in a loop consumes numbers from the sequence until it's where you need it to be.

Or, if it is a lot of numbers this:

-- First, alter the object so the next increment will jump 1000 instead of just 1.
alter sequence myschema.seq_mysequence increment by 1000;

-- Run a select to actually increment it by 1000
select myschema.seq_mysequence.nextval from dual;

-- Alter the object back to incrementing only 1 at a time
alter sequence myschema.seq_mysequence increment by 1;
Community
  • 1
  • 1
Thilo
  • 257,207
  • 101
  • 511
  • 656
  • 1
    I use the ALTER SEQUENCE method all the time, it's just 3 statements. – Jeffrey Kemp Jun 08 '12 at 04:55
  • DROP and recreate also seems to work (but invalidates all objects that depend on the sequence temporarily). – Thilo Jun 08 '12 at 04:56
  • 2
    DROP and recreate also loses any grants on the sequence. Really, if all you want to do is change the sequence's next value, dropping it is an unnecessary complication. – Jeffrey Kemp Jun 08 '12 at 05:02