Add Identity Column to Existing Table

[jasperputs]

Hello everyone. I am working with tables that need an identity column. I currently have a view in which I cast the different columns to the data type that I want. Now I want the result of this view to be inserted or merged into a table. The schema of the view can be reused, but an identity column (Start at 1, increment by 1) should be added.

However, the only option that I see, is that I define the schema of the target table somewhere (and thus write it down). I don't want this, as I would need to manage the schema in two locations (in the view where I do the casts, and the schema of the target table, although the latter script should only be executed once).

Is there a possibility to take the schema of the view, add the identity column and use this as the schema for a new table?

I already tried to create a table from the view (which worked), but if I then altered the table and added the identity column, I got an error. It seems that the identity column can only be added during the creation of a table.

I got the following error:

[-werners-]

At the moment that is not supported.

The alter table command only allows for sync identity.

[Kaniz]

Hi @Jasper Puts​, We haven’t heard from you on the last response from @Werner Stinckens​, and I was checking back to see if his suggestions helped you. Or else, If you have any solution, please share it with the community as it can be helpful to others.

[jasperputs · Accepted solution]

Hello @Kaniz Fatma​, the answer from @Werner Stinckens​ helped. I just created a script that is used to create the table. In the script I specify the identity column and this works.

[Kaniz]

Hi @Jasper Puts​, Thank you for your response. I'm glad the solution worked. Would you mind selecting the best answer for the community?

[ramankr48]

Hello @Jasper Puts​ how did you solve this issue of creating a identity column to existing table.

I'm also getting the same error as you got.

[ramankr48]

the recent syntax is :

ALTER TABLE retaildb.sales_order ADD COLUMN sales_key3 BIGINT GENERATED ALWAYS AS IDENTITY(1,1);