February 10, 2009 at 9:34 am
Let's say I identified 5,000 records with
CUST_ID = NULL
I need to assign new CUST_ID in incremental order
starting let's say from 1200.
Is there any SQL that can do this without using a loop?
February 10, 2009 at 10:13 am
Yes it can.
This solution doesn't involve a tally table, I just used it to set up the test data. But if you don't have one look here: http://www.sqlservercentral.com/articles/TSQL/62867/
The solution involves using a cte to pull all the rows that were null and using the ROW_NUMBER function, ordered by another column, to generate a set of sequential new ID numbers. This CTE was then used to update the original, essentially joining on the same column that was used to dictate the order of the ROW_NUMBER function.
WARNING: You must have one or more columns that will produce a unique identification of each row, or you won't get the results you want when you run the update statement.
You should also be aware that the code below does no checking to see if an ID number already exists in the table before the update occurs. It simply illustrates how a batch update for sequential IDs can be performed.
-----------------------------------------------------------------------------------------------
-- declare and populate a test table of 10,000 rows
declare @test-2 table (id int, blah varchar(20) PRIMARY KEY)
insert into @test-2
select case when N <=1200 then N
when N >=6500 then N
else null
end
, 'blah '+ cast(N as varchar(5))
from tally
where N <= 10000
--select top 1300 *
--from @test-2
---------------------------------------------------------------------------------------
-- the solution begins here
---------------------------------------------------------------------------------------
;with cteFixit as
( select blah as xblah, row_number() over(Order by blah) as newIDno
from @test-2
where ID is null
)
-- select * from cteFixit
update @test-2
set ID = newIDno + 1200 -- 1200 was your arbitrary starting point
from cteFixit
where blah = xblah -- runs much faster if table to be updated has an index over blah
select * from @test-2
where ID between 1000 and 2000
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 10, 2009 at 11:44 am
Here is my schema:
create table stg.STG_CUSTOMER_INTEG1
(
RECORD_ID int identity(1,1),
CUST_ID int,
CUST_NAME varchar(100)
)
CUST_IDRECORD_IDCUST_NAME
----------------------------------
NULL 493164RBC Group
NULL 865669CIBC
NULL 865670TD Canada Trust
NULL 865671RBC Royal Bank
NULL 865672Royal Bank
I couldn't understand why I need "Tally" table
so I just ignored it and ran the following code:
;with cteFixit as
(
SELECT RECORD_ID as xblah, row_number() over(Order by RECORD_ID) as newIDno
FROM stg.STG_CUSTOMER_INTEG1
WHERE CUST_ID IS NULL
)
update stg.STG_CUSTOMER_INTEG1
set CUST_ID = newIDno + 1200 -- 1200 was your arbitrary starting point
from cteFixit
where RECORD_ID = xblah
and got what I wanted:
CUST_IDRECORD_IDCUST_NAME
----------------------------------
1201 493164RBC Group
1202 865669CIBC
1203 865670TD Canada Trust
1204 865671RBC Royal Bank
1205 865672Royal Bank
Am I doing the right thing?
February 10, 2009 at 12:23 pm
Looks good to me, if it looks good to you. 😀
The tally table was totally unnecessary to the solution, but I included the article reference in case anyone wanted to run my test script. That way, they would know how to create one.
Bob Hovious (2/10/2009)
Yes it can.This solution doesn't involve a tally table, I just used it to set up the test data...... 😎
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 10, 2009 at 2:32 pm
Thank you very much Bob.
This is a really good stuff.
February 10, 2009 at 2:35 pm
You're very welcome. I just hope they are the right cust_ids 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply