How to update ID=null values in table to incremental counter values?

Question

On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows:

ID   NaturalID               Comment

1    franknfurther03071972   blahblah
2    chrisrock12081980       nonsense
null clairecampbell24121990  merry christmas
3    walterhermes22032001    young guy
4    tanjaolsen16051996      nice
null timharris20041999       came late

The rows with "null" IDs are new, the numbered IDs are those already existing in the main, target table. The NaturalID can uniquely identify an entry (in fact, it's multiple columns). I want to set the "null" IDs to incremental values, following the current max ID, here: 5 and 6, increasing when more null IDs are found.

Currently, I use a cursor to iterate over the rows with ID null and update each ID with a value, but sincce it's really a very big table, it would take days.

I tried to do an update with row_number(), but it gives me an error "Windowed functions can only appear in the SELECT or ORDER BY clauses.":

update StagingTable set ID=ROW_NUMBER() over (order by NaturalId)
from StagingTable where id is null  -- fails

How can I do it?

Answer 1

You can do this.

WITH T
     AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + 
                    ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID,
                ID
         FROM   StagingTable
         WHERE  ID IS NULL)
UPDATE T
SET    ID = New_ID 

So the windowed function is used in the SELECT list but you can still use the result of it to UPDATE the column.

You should probably have a filtered index unique constraint on ID WHERE ID IS NOT NULL to prevent duplicates too. Or run this at serializable isolation level to block concurrent inserts.

Answer 2

I finally worked around the problem using SQL Server 2012 sequences. Unfortunately, this won't work with versions prior to 2012. Speed was o.k, one or two minutes for about 20 million IDs to insert.

declare @old_max_id int -- get somehow
declare @sql nvarchar(2000)

set @sql=N'CREATE SEQUENCE INSERT_IDS START WITH ' + str(@old_max_id + 1) + N' INCREMENT BY 1'
EXEC sp_executesql @sql
-- dynamic creation required due to variable start value.

UPDATE StagingTable SET ID=NEXT VALUE FOR INSERT_IDS WHERE ID IS NULL

DROP SEQUENCE INSERT_IDS

This is not the original code I used, so check for syntax errors first. I also checked for an existing sequence first and dropped it, if there.

If somebody knows a fast working solution for SQL Server versions without sequences, that would still be nice to see!