Post reply

Increment Identity Column Value

  • June 25, 2011 at 2:09 am

    #240474

    Can we increment Indentity field value with a sql ?

  • June 25, 2011 at 2:30 am

    #1344055

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


    Clear Sky SQL
    My Blog[/url]

  • June 25, 2011 at 2:46 am

    #1344057

    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

  • June 25, 2011 at 12:42 pm

    #1344114

    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)

  • June 25, 2011 at 1:30 pm

    #1344134

    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]

  • June 25, 2011 at 9:29 pm

    #1344173

    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.

  • June 25, 2011 at 11:50 pm

    #1344180

    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)

  • June 27, 2011 at 3:31 am

    #1344362

    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/

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

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