Increment Identity Column Value

Question

Post reply

Increment Identity Column Value

kamaleshray

SSC Enthusiast

Points: 109
More actions
June 25, 2011 at 2:09 am

#240474

Can we increment Indentity field value with a sql ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

An identity column automatically increments, can you explain your issue further ?

Clear Sky SQL
My Blog[/url]

kamaleshray SSC Enthusiast Points: 109 More actions · Answer 2

Hi Dave Ballantyne,

Thanks for reply, Actualy I want to set the next value for Identity .

But now i got one sql to do this

DBCC CHECKIDENT (TableName , RESEED, value fo identity column)

example

To set identity = 999 on table 'order' we can do

DBCC CHECKIDENT (orders, RESEED, 999)

Thanks

Kamalesh

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

kamaleshray (6/25/2011)
Hi Dave Ballantyne,
Thanks for reply, Actualy I want to set the next value for Identity .
But now i got one sql to do this
DBCC CHECKIDENT (TableName , RESEED, value fo identity column)
example
To set identity = 999 on table 'order' we can do
DBCC CHECKIDENT (orders, RESEED, 999)
Thanks
Kamalesh

Why do you want to do this? I mean, what is the business logic/requirement behind this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 4

Agreeing with Jeff.

Reseeding is something you should rarely (if ever) need to do.

What are you trying to achieve ?

Clear Sky SQL
My Blog[/url]

raistlinx Ten Centuries Points: 1345 More actions · Answer 5

I've seen data migration projects do this where you were generating a new lookup table for migrating many environments into one. Each environment would get a seed number and then increment from that for each new lookup value created for the old codes.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 6

raistlinx (6/25/2011)
I've seen data migration projects do this where you were generating a new lookup table for migrating many environments into one. Each environment would get a seed number and then increment from that for each new lookup value created for the old codes.

Agreed that you could do that (although I wouldn't do it quite that way) but I'd like to know why the OP wants to do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

taybre SSC Eights! Points: 916 More actions · Answer 7

Can we increment Indentity field value with a sql ?

using below code as an example:

create table #temp

(

[id]int identity(7,1),

[col1] varchar(10),

[col2] varchar(20)

)

Whenever you insert into the above table (#temp) values for [col1] and [col2] the [id] column will increment by one (shown by the second value in the identity definition. This first value in the identity definition (7) will be the starting figure.

So, if you then use the following code.

insert into #temp

select 'James','Apple'

union all

select 'George','Banana'

union all

select 'Freddy','Pineapple'

union all

select 'Max','Mango'

select * from #temp

You will see that the results look like this.

idcol1col2

7JamesApple

8GeorgeBanana

9FreddyPineapple

10MaxMango

You can increment by whatever number you want, but I recommend using '1' as default. Same for the starting figure, start at either 1 or 0.

HTH

taybre :-):-)

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/