How can I insert-select a sequence starting from a variable?-CodePudding

There was a question here on SO that was since then removed. But while I was researching for ways to solve it, I was writing a script that avoids the use of an identity column and uses a sequence itself:

create table table1(Id int primary key, group_id int, Name varchar(64))
insert into table1(Id, group_id, Name) values (1, 1, 'a'), (2, 1, 'b'), (4, 1, 'c'), (8, 1, 'd')
declare @MaxId as int
select @MaxId = max(Id)   1 from table1
declare @sql varchar(max)
set @sql = N'CREATE SEQUENCE MySequence  AS INTEGER START WITH '   cast(@maxId as varchar(10))
exec(@sql)
insert into table1(id, group_id, Name)
select next value for MySequence, 2, Name
from table1
where group_id = 1;

This actually works, that is, it successfully inserts four records with dynamically generated ids.

However, the the part of

declare @sql varchar(max)
set @sql = N'CREATE SEQUENCE MySequence  AS INTEGER START WITH '   cast(@maxId as varchar(10))
exec(@sql)

is very much counter-intuitive and hacky in my opinion.

Question: Is there a way to define a sequence that starts from a variable's value without generating a text and execute it?

CodePudding user response：

The CREATE SEQUENCE syntax documentation shows a constant is required so you cannot specify a variable in the DDL statement.

CodePudding user response：

Yes, creating a single-use dynamic sequence is a hack.

Instead use ROW_NUMBER(), something like

use tempdb
drop table if exists table1
go
create table table1(id int, group_id int, name varchar(200))

insert into table1(id,group_id,name) values (1,1,'a')
insert into table1(id,group_id,name) values (2,1,'a')

declare @maxValue int = (select max(id) from table1)

insert into table1(id, group_id, Name)
select @maxValue   row_number() over (order by Id), 2, Name
from table1
where group_id = 1;


select * from table1