amending a column to increment while inserting; then switching back

  • Hello

    I have a table that has a column id with a data type of bigint

    This is built using Cognos data manager which has a funny little (non-identity) way of incrementing the counter

    There's quite a few build routines that are reliant upon this been bigint

    i.e. I want to leave this Cognos DM build intact and use SSIS for the next part

    I have a new datasource and want to use this to append to the existing table

    Can I switch to identity, upsert, then switch back?

    Or is there some other method I should be using

    I've tried

    set identity_insert <table> on

    This doesn't work

    Ideally, I want to use T-SQL

    Thanks

    Damian.

    - Damian

  • The IDENTITY property can't be added to an existing column nor removed from a column.

    You could add an identity column to the table, if it doesn't have one, and then adjust the next identity value to be assigned, using:

    DBCC CHECKIDENT ( ... RESEED )

    Then use that value to assign a value to another column, possibly in a trigger. Then you could reset the identity value with another RESEED.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • This is the 2008 forum, so I suspect this won't help, but if you're on 2012 or better, you could look at using SEQUENCE to possibly do what you're looking for.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • You may add ROW_NUMBER to the recordset being inserted and add its values to MAX (ID ) of the table:

    begin transaction

    declare @MaxID bigint

    select @MaxID + MAX(ID) From TargetTable (TABLOCKX)

    INSERT INTO TargetTable

    (ID, ...)

    SELECT @MaxID + ROW_NUMBER() over (...), ...

    FROM Sourse

    IF @@ERROR = 0

    COMMIT TRANSACTION

    ELSE

    ROLLBACK

    _____________
    Code for TallyGenerator

  • Thanks for the advice

    Unfortunately, it is 2008 so SEQUENCE is not an option

    I'm going to look into the proposal by sergiy

    For my purposes, I think I can remove all the relevant records and repopulate from the max id counter

    upserts and attempting to use an SCD that holds history is probably going to over-complicate it

    I'll hold history elsewhere and just focus on getting the current data into this table

    Thanks

    - Damian

  • Sergiy (10/11/2016)


    You may add ROW_NUMBER to the recordset being inserted and add its values to MAX (ID ) of the table:

    begin transaction

    declare @MaxID bigint

    select @MaxID + MAX(ID) From TargetTable (TABLOCKX)

    INSERT INTO TargetTable

    (ID, ...)

    SELECT @MaxID + ROW_NUMBER() over (...), ...

    FROM Sourse

    IF @@ERROR = 0

    COMMIT TRANSACTION

    ELSE

    ROLLBACK

    Nice idea, Sergiy.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Using your suggestion sergiy, I've managed to get it working

    One question

    You have:

    declare @MaxID bigint

    select @MaxID + MAX(ID) From TargetTable (TABLOCKX)

    This returns NULL

    I've amended it to (changed + for =)

    declare @MaxID bigint

    select @MaxID = MAX(ID) From TargetTable (TABLOCKX)

    Was it a typo or have I missed something fundamental?

    Thanks

    Damian.

    - Damian

  • DamianC (10/12/2016)


    Using your suggestion sergiy, I've managed to get it working

    One question

    You have:

    declare @MaxID bigint

    select @MaxID + MAX(ID) From TargetTable (TABLOCKX)

    This returns NULL

    I've amended it to (changed + for =)

    declare @MaxID bigint

    select @MaxID = MAX(ID) From TargetTable (TABLOCKX)

    Was it a typo or have I missed something fundamental?

    Thanks

    Damian.

    Typo. Well spotted.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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