Post reply

Set Row Identity from a Variable

  • December 12, 2012 at 9:39 am

    #271623

    Hello Everyone

    I am working on a strange database, as some can tell from my other posts.

    I need to be able to create a Table Variable, a Hard Table or a Temp Table with an IDENTITY column, and have the starting numeric value as a variable

    RowID int IDENTITY(@NextRowNumber, 1)

    I remember doing this long ago, but not sure how I made this happen.

    Thank you in advance for your assistance and suggestions

    Andrew SQLDBA

  • December 12, 2012 at 10:20 am

    #1567989

    one option is to create the table with an IDENTITY (1,1) and then a DBCC CHECKIDENT to reseed the table at the given value passing a variable to it

    IF OBJECT_ID('dbo.Test') IS NOT NULL

    DROP TABLE dbo.Test

    CREATE TABLE Test (ID INT IDENTITY(1,1), SomeCol CHAR(1))

    INSERT INTO Test (SomeCol) VALUES ('A')

    DECLARE @NewSeed INT = 100 --(SELECT SomeCol from SomeTable WHERE OnlyOneRow = Returned)

    DBCC CHECKIDENT ("dbo.Test", RESEED, @NewSeed)

    INSERT INTO Test (SomeCol) VALUES ('B')

    SELECT * FROM Test

    The other option is dynamic sql which has the injection issue you would have to take care of.

    EDIT: Make sure to Reseed at 1 lower than what you want the seed to start at as the next insert will be the Reseed value + 1

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • December 12, 2012 at 10:23 am

    #1567991

    Thanks Captain

    That is exactly what I ended up doing. I found it in the SQL BOL right after posting on here.

    Thanks for your time and suggestion, it works perfectly

    Andrew SQLDBA

  • December 12, 2012 at 10:25 am

    #1567992

    No problem. the only issue is i dont think it will work with the table variables. for those you may need dynamic sql or just use a temp table instead.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • December 12, 2012 at 1:53 pm

    #1568061

    I found that out about the table variable, so i now use a temp table. Not a big deal. I would much rather use a table variable, especially for this small dataset. But oh well, no need to get ones panties in a wad. LOL

    Thanks again, you have been a great help

    Andrew SQLDBA

  • December 12, 2012 at 1:55 pm

    #1568062

    AndrewSQLDBA (12/12/2012)

    I found that out about the table variable, so i now use a temp table. Not a big deal. I would much rather use a table variable, especially for this small dataset. But oh well, no need to get ones panties in a wad. LOL

    Thanks again, you have been a great help

    Andrew SQLDBA

    not a problem and good luck on your other issues in this DB

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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