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?