%sql
WITH genCTE AS (
SELECT MAX(PredID) + 1 AS PredID, 145 AS SystemID
FROM TableA
UNION ALL
SELECT PredID + 1
FROM genCTE
)
SELECT * FROM genCTE
When I attempt this, I get an error that genCTE does not exists.
There may be a better way to what I am trying to do. Currently in the version of databricks we are running, we cant use an identity column. We are migrating a table and job from SQL Server to databricks. I can do a full load and pull over the identity column. But, I need to continue to populate this identity column going forward.
I also have a tried a SQL UDF where I can easily grab the MAX(ID) and have tried a row_number function over the top of it, but I cant get it work.
Hopefully I have done an adequate job of describing this issue. I simply need continue the IDs and need to do it programmaticly in databricks. I am hoping I can do this via SQL as well as I am not at all familiar with Python, although I would be open to a Python solution, I would just need a little more hand holding with it.
Thank you!