Set Row Identity from a Variable

Question

Set Row Identity from a Variable

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
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

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

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

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]

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

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

CapnHector SSCoach Points: 16143 More actions · Answer 3

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]

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

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

CapnHector SSCoach Points: 16143 More actions · Answer 5

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]