CODEWITHSUNDEEP
sql-server
VKB
VKB

Reputation: 147

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

I am trying to do something like in SQL server 2012 

CREATE SEQUENCE item_seq
  START WITH (SELECT MAX(i_item_sk)
     FROM item) 
  INCREMENT BY 1;

Is it possible? What are the other ways if this is not possible? Can we do it like how we do it in PostgreSQL(given below)?

create sequence item_seq 
select setval('item_seq', (select max(i_item_sk)+1 from item), false);

I would be further using this sequence variable in Kettle 'Add sequence' step.

Upvotes: 12

Views: 9140

Answers (1)

Jason Whitish
Jason Whitish

Reputation: 1438

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

Upvotes: 15

Related Questions