OraFAQ Forum: SQL & PL/SQL » Populating Primary Key Column using Sequence

Home » SQL & PL/SQL » SQL & PL/SQL » Populating Primary Key Column using Sequence (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Populating Primary Key Column using Sequence [message #606537]

Sun, 26 January 2014 01:46

BS_99
Messages: 34
Registered: March 2010

Member

Hi All,
i have a sequence called Seq_A:

Create sequence Seq_A
start with 1
incremented by 1
nocache;

Now Seq_A.Currval is 50 after some transactions.

I have an existing table called Tab_A

Create Table Tab_A(TID NUMBER PRIMARY KEY,
TNAME VARCHAR2(50));

Presently Tab_A is Having 1000 records and the values in TID is densely populated from 1 to 1000.

Now on i want to Use Seq_A to populate the TID of Tab_A.
How Can i do this?

Report message to a moderator

Re: Populating Primary Key Column using Sequence [message #606539 is a reply to message #606537]

Sun, 26 January 2014 01:52

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The simplest way is to recreate the sequence (i.e. drop it and create it so that it starts with 1001).

Report message to a moderator

Re: Populating Primary Key Column using Sequence [message #606540 is a reply to message #606539]

Sun, 26 January 2014 02:05

BS_99
Messages: 34
Registered: March 2010

Member

Thanks for reply.
But is there any other way than dropping the Sequence?

Report message to a moderator

Re: Populating Primary Key Column using Sequence [message #606541 is a reply to message #606540]

Sun, 26 January 2014 02:06

BS_99
Messages: 34
Registered: March 2010

Member

also i don't want to write PL/SQL Loop which will increment the seq to 1001.
Looking for some other alternative if any?

Report message to a moderator

Re: Populating Primary Key Column using Sequence [message #606542 is a reply to message #606541]

Sun, 26 January 2014 02:09

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

ALTER SEQUENCE is capable of doing that: first modify INCREMENT value so that the next fetch reaches the "target" value, and then modify INCREMENT again. Here's an example.

SQL> select seq_a.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence seq_a increment by 999;

Sequence altered.

SQL> select seq_a.nextval from dual;

   NEXTVAL
----------
      1000

SQL> alter sequence seq_a increment by 1;

Sequence altered.

SQL> select seq_a.nextval from dual;

   NEXTVAL
----------
      1001

SQL>

Report message to a moderator