Populating a new column in a SQL2K table with incremental numbers by row

  • Hi all. I hope you can assist - I've been digging around here and on other sites and can't find exactly what I need and how to do it. A new business need requires a fresh set of ID numbers for our research participants to comply with our parent company procedures.

    I'm trying to populate a blank column in a SQL2K database table with consecutive numbers by row. I already have an IDENTITY column, and adding a second one in SQL2K is not allowed.

    Here's a simplified example of the current table data. We'll assume the table has only three rows. PK_ID is the existing primary key (Identity(1,1))

    PK_ID

    3

    4

    5

    I need to add column NEW_ID (char(9)) and populate it using the rubric 'LB' + incremented number. The number starts at 1000001. This is an example of the updated table I want:

    PK_ID NEW_ID

    3 LB1000001

    4 LB1000002

    5 LB1000003

    Any thoughts you may have would be greatly appreciated!

    PS - I'll be adding additional NEW_ID values as we recruit new study participants, but I'll manage that through the application. I just need to get these assigned as above to get me started.:-D

  • Questions:

    1. Do you have a clustered index on the identity column?

    2. Will the ID #'s ever need to be reset (ie changing account #, restart at 1)?

    3. Is the "1" in the "LB1" fixed, with the incrementing number being the six digits after it? Or is it the seven digits after the "LB" that need incrementing?

    4. Is every row going to get the next ID# based on it's value of the identity column?

    ie:

    Identity column New ID

    1 LB1000001

    2 LB1000002

    3 LB1000003

    5 LB1000004

    8 LB1000005

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • There are basically two approaches:

    1) insert the data into a new table with an IDENTITY column to get consecutive New_ID's (maybe using an auxiliary column to get it formatted properly) or

    2) use the "Quirky update" method to assign the New_ID values.

    I guess Waynes questions are there to determine which solution will apply here... Right, Wayne?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hm, Identity column seeded at 1000001 and then obfuscate that into the 'LB' format with a calculated column should work as well.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • LutzM (11/16/2010)


    I guess Waynes questions are there to determine which solution will apply here... Right, Wayne?

    Absolutely - I'm leaning towards the quirky update right now. The OP already has the extra column, so this should be pretty easy to implement.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Craig Farrell (11/16/2010)


    Hm, Identity column seeded at 1000001 and then obfuscate that into the 'LB' format with a calculated column should work as well.

    Except that there is already an identity column on that table...

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • @Wayne, the answers to your questions:

    1. Primary key index is not clustered.

    2. I won't need to restart the numbering

    3. The "1" is not fixed, "LB" is the new_id prefix, and the following 7 digit number should increment

    4. Based on the example you provided, yes. Each successive row will increment up by 1.

    Thanks everyone for your responses!

  • Okay, so we can't do it the way I was thinking of.

    What we're going to do:

    1. Create a temporary table with an identity column that starts at 1000001

    2. Copy existing data into the temporary table in order of the PK. By copying in order, the rows will get the new value in the proper order.

    3. Update the permanent table with the new value.

    -- Test data

    DECLARE @test-2 TABLE (PK_ID INT PRIMARY KEY NONCLUSTERED, NEW_ID CHAR(9) NULL);

    INSERT INTO @test-2 (PK_ID)

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 5 UNION ALL

    SELECT 8 UNION ALL

    SELECT 10;

    -- Create a temporary table with an identity column that starts at 1000001

    IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP;

    CREATE TABLE #TEMP (PK_ID INT PRIMARY KEY CLUSTERED, NEW_ID INT IDENTITY (1000001,1));

    -- Copy existing data into the temporary table in order of the PK.

    -- By copying in order, the rows will get the new value in the proper order.

    INSERT INTO #TEMP

    SELECT PK_ID

    FROM @test-2

    ORDER BY PK_ID;

    -- Update the permanent table with the new value.

    UPDATE ot

    SET NEW_ID = 'LB' + convert(varchar(7), t1.NEW_ID)

    FROM @test-2 ot

    JOIN #TEMP t1

    ON t1.PK_ID = ot.PK_ID;

    -- Show the results.

    SELECT * FROM @test-2;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • @Wayne - that worked great - thank you!

    I did make one error in my answers to your questions: the PK is clustered. Don't know if that would have made a difference in which approach you decided to take, but in any case this code works beautifully.

    Many thanks! Thanks to everyone else for responding as well. 🙂

  • Clinton Finch (11/17/2010)


    @Wayne - that worked great - thank you!

    I did make one error in my answers to your questions: the PK is clustered. Don't know if that would have made a difference in which approach you decided to take, but in any case this code works beautifully.

    Many thanks! Thanks to everyone else for responding as well. 🙂

    Glad it worked for you so good.

    With a clustered PK on the PK_ID column, we wouldn't have needed to dump the data into a temp table and then update the real table - the update could have been done in one step on the real table.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 10 posts - 1 through 9 (of 9 total)

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