Menu
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.
737
To load data into a table and generate a surrogate key by using IDENTITY, create the table and then use INSERT.. SELECT or INSERT..VALUES to perform the load. It's not possible to use CREATE TABLE AS SELECT currently when loading data into a table with an IDENTITY column.
Use the surrogate key transformation to add an incrementing key value to each row of data. This is useful when designing dimension tables in a star schema analytical data model. In a star schema, each member in your dimension tables requires a unique key that is a non-business key.
A surrogate key in SQL Server is created a by assigning an identity property to a column that has a number data type. A surrogate key is a value generated right before the record is inserted into a table. There are several reasons to replace a natural key with a surrogate key.
It turns out a surrogate key is very similar to a primary key in that it is a unique value for an object in a table. However, rather than being derived from actual data present in the table, it is a field generated by the object itself.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
