Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hello,
What is an alternative way to generate an identity column when using a Warehouse?
Thanks.
Solved! Go to 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!
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.
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
@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!
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 |