This script is useful when you have to import the sequence value from the source to the target schema, keeping the CURRVAL the same in the target schema. It will replace the old value of the sequence with the given new sequence value provided through a flat file consisting of sequence name and new sequence value:
-- Pass a flat file consisting of Sequence Name and New Values as a parameter to this sql file. -- set_new_seq_values.sql define SEQ_NAME=&1 define NEW_VAL=&2 set pages 0 set ver off set wrap off set feed off set term off col old_inc_val noprint new_value old_inc_val col cur_inc_val noprint new_value cur_inc_val select INCREMENT_BY old_inc_val from user_sequences where sequence_name='&SEQ_NAME'; select ( &NEW_VAL - &SEQ_NAME..NEXTVAL ) cur_inc_val from dual; alter sequence &SEQ_NAME INCREMENT BY &&cur_inc_val; select &SEQ_NAME..NEXTVAL from dual; alter sequence &SEQ_NAME INCREMENT BY &&old_inc_val; set term on prompt select 'Sequence &SEQ_NAME has been set to &NEW_VAL..' from dual; prompt EXIT
For More Information
- What do you think about this tip? E-mail us at
- editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL gurus are waiting to answer your technical questions.
This was first published in May 2001