How to Create Sequence Using Starting Value from Query

How to create sequence using starting value from query?

you can use setval() after you created the sequence:

CREATE SEQUENCE book_id_seq;
select setval('book_id_seq', (SELECT MAX(id) + 1 FROM book.book));

Create a Sequence with START WITH from Query

The START WITH CLAUSE accepts an integer. You can form the "Create sequence " statement dynamically and then execute it using execute immediate to achieve this.

declare
    l_new_seq INTEGER;
begin
   select max(id) + 1
   into   l_new_seq
   from   test_table;

    execute immediate 'Create sequence test_seq_2
                       start with ' || l_new_seq ||
                       ' increment by 1';
end;
/

Check out these links.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_6014.htm

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm

How to create an Oracle sequence starting with max value from a table?

you might want to start with max(trans_seq_no) + 1.

watch:

SQL> create table my_numbers(my_number number not null primary key);

Table created.

SQL> insert into my_numbers(select rownum from user_objects);

260 rows created.

SQL> select max(my_number) from my_numbers;

MAX(MY_NUMBER)
--------------
           260

SQL> create sequence my_number_sn start with 260;

Sequence created.

SQL> insert into my_numbers(my_number) values (my_number_sn.NEXTVAL);
insert into my_numbers(my_number) values (my_number_sn.NEXTVAL)
*
ERROR at line 1:
ORA-00001: unique constraint (NEIL.SYS_C00102439) violated

When you create a sequence with a number, you have to remember that the first time you select against the sequence, Oracle will return the initial value that you assigned it.

SQL> drop sequence my_number_sn;

Sequence dropped.

SQL> create sequence my_number_sn start with 261;

Sequence created.

SQL>  insert into my_numbers(my_number) values (my_number_sn.NEXTVAL);

1 row created.

If you're trying to do the 'gapless' thing, I strongly advise you to

1 not do it, and #2 not use a sequence for it.

How to set 'start with' of sequence to select query result in SQL server?

It does not look like you can declare a variable amount in the syntax. However, you can wrap it in an EXEC statement, like so:

DECLARE @max int;
SELECT @max = MAX(i_item_sk)
     FROM item

exec('CREATE SEQUENCE item_seq 
    START WITH ' + @max +
'   INCREMENT BY 1;')

select * from sys.sequences

How to create a sequence with start with value that is a query

Make all the text in a single line. Or at least each literal.

Also, what is the product/utility and product version ?

How to generate Sequence starting with Max Id of a table

Unfortunately, no variables here. Use dynamic SQL instead:

declare @maxBookingId as int 
select @maxBookingId = max(bookingid) from booking
declare @s nvarchar(4000);
set @s = N'
CREATE SEQUENCE Invoice_Seq  AS INTEGER  
START WITH ' + cast(@maxBookingId as nvarchar) + '
INCREMENT BY 1  
NO CYCLE;'

EXEC (@s);

PostgreSQL: starting a sequence at MAX(the_column)+1

You can't specify a dynamic value for the start value.

But you can set the value once the sequence is created:

CREATE SEQUENCE my_sequence MINVALUE 1000000 OWNED BY my_table.id_column;
select setval('my_sequence',  (SELECT MAX(id_column) FROM my_table));

---

Related Topics