Search code examples
azuredata-warehouseazure-synapseparallel-data-warehouse

Azure SQL Data Warehouse Surrogate Keys

So Azure SQL Data Warehouse doesn't support identity columns, and therefore it's tricky to deal with surrogate keys.. anyone got any bold solutions on this one?

This is best i have found, and it's pretty horrific.

Solution

  • That is the best option - but you can use a constant value in your OVER clause to avoid having to sort on a particular value, and you don't need to use a variable.

    INSERT INTO testTgtTable (SrgKey, colA, colB)
SELECT
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) + (SELECT ISNULL(MAX(SrgKey),0) SK FROM dbo.testTgtTable) SK
  , [colA]
  , [colB]
FROM testSrcTable;