Code to populate aritficial key

• Johnny B

  SSCrazy

  Points: 2965

  More actions

  January 26, 2016 at 2:05 pm

  #307924

  Hi,

  We just added the source system primary key to our warehouse and managed to bring 90% the keys over my matching on a set of logical columns. For the remaining %10 we have decided to insert negative numbers to easily indicate that these are keys artificial.

  I'm looking for an update statement which will populate the empty rows. the first empty row should have -1, the next -2, -3 etc. Any ideas? Remember not all the

  John

  SQL 2012 Standard VPS Windows 2012 Server Standard

• Ken McKelvey

  SSCoach

  Points: 18856

  More actions

  January 26, 2016 at 2:22 pm

  #1854556

  Try something like:

  WITH RNs

  AS

  (

  SELECT *

  ,-ROW_NUMBER() OVER (ORDER BY <WhatEverYouWant>) AS rn

  FROM YourTable

  WHERE YourPK IS NULL

  )

  UPDATE RNs

  SET YourPK = rn;