November 16, 2010 at 2:50 pm
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
November 16, 2010 at 3:32 pm
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
November 16, 2010 at 4:06 pm
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?
November 16, 2010 at 4:11 pm
Hm, Identity column seeded at 1000001 and then obfuscate that into the 'LB' format with a calculated column should work as well.
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
November 16, 2010 at 6:13 pm
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
November 16, 2010 at 6:15 pm
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
November 16, 2010 at 8:05 pm
@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!
November 17, 2010 at 5:00 am
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
November 17, 2010 at 9:21 am
@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. 🙂
November 17, 2010 at 11:20 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply