Alternative to Identity columns in Warehouse

[Trystan]

Hello,

What is an alternative way to generate an identity column when using a Warehouse?

Thanks.

[mikeburek]

Just an interesting note, as of now, you can create temporary tables with an identity column. Not sure when that started.

DROP TABLE IF EXISTS #tempTable
CREATE TABLE #tempTable (a INT IDENTITY (1, 1), b int)
INSERT INTO #tempTable (b) VALUES (1)
INSERT INTO #tempTable (b) VALUES (1)
INSERT INTO #tempTable (b) VALUES (1)
INSERT INTO #tempTable (b) VALUES (2), (2), (2), (2)
INSERT INTO #tempTable 
SELECT 3 FROM #tempTable 

SELECT * FROM #tempTable

--------------------

But, as of now, you cannot use a permanent table in the same query as a temporary table, so you cannot additionally try to insert this temporary table into a permanent table.

Does not work:

DROP TABLE IF EXISTS permanentTable
CREATE TABLE permanentTable (a INT, b INT)
INSERT INTO dbo.permanentTable (a, b)
SELECT a, b FROM #tempTable

Error: Msg 15816, Level 16, State 3, Line 17
The query references an object that is not supported in distributed processing mode.

[mikeburek]

Adding a note from the future:
There is now a full example of this workaround on Microsoft Learn / Documentation:
https://learn.microsoft.com/en-us/fabric/data-warehouse/generate-unique-identifiers

[puneetvijwani · Accepted solution]

@Trystan  you can use ROW_NUMBER() to mannually generate them via sql queries and add the data in your table

Ps: If my response helped, kindly select it as the solution. Your kudos are greatly appreciated!