Post reply

populate value in create sequence

  • July 29, 2015 at 3:09 pm

    #319415

    Hi friends,

    I'm looking to see if there is a way to populate starting number for the sequence from a max value of a table.

    CREATE SEQUENCE test_seq

    AS [int]

    START WITH (select max(col1)+1000 from table1)

    INCREMENT BY 1

    MINVALUE 1

    MAXVALUE 2147483647

    CACHE

    GO

    Please help...Thank you so much

  • July 30, 2015 at 9:43 am

    #1816192

    Has anyone done something like this? please share.. thanks in advance

  • July 30, 2015 at 10:01 am

    #1816208

    Have you tried Dynamic SQL? In a hurry but I threw this together.

    DECLARE @test-2 TABLE (SomeValue INT)

    INSERT INTO @test-2

    VALUES (2000),(2001),(2002)

    SELECT * FROM @test-2

    DECLARE @maxValue INT

    DECLARE @sqlstmt NVARCHAR(200)

    SET @maxValue = (SELECT MAX(SomeValue)+1000 FROM @test-2)

    SET @sqlstmt =

    '

    CREATE SEQUENCE test_seq

    AS INT

    START WITH ' + CONVERT(VARCHAR(10),@maxValue) +

    ' INCREMENT BY 1

    MINVALUE 1

    MAXVALUE 2147483647

    CACHE

    '

    EXECUTE sp_executesql @sqlstmt;

    SELECT NEXT VALUE FOR test_seq

    SELECT NEXT VALUE FOR test_seq

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • July 30, 2015 at 10:25 am

    #1816226

    Thank you so much!!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply