topic Add Identity Column to Existing Table in Data Engineering

Add Identity Column to Existing Table

jasperputs — Mon, 20 Jun 2022 13:54:15 GMT

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:

Re: Add Identity Column to Existing Table

-werners- — Tue, 21 Jun 2022 08:51:18 GMT

At the moment that is not supported.

The alter table command only allows for sync identity.

Re: Add Identity Column to Existing Table

jasperputs — Tue, 28 Jun 2022 08:23:46 GMT

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.

Re: Add Identity Column to Existing Table

ramankr48 — Tue, 13 Sep 2022 07:10:25 GMT

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.

Re: Add Identity Column to Existing Table

ramankr48 — Tue, 13 Sep 2022 07:11:36 GMT

the recent syntax is :

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

Re: Add Identity Column to Existing Table

Vittal2 — Fri, 24 Jan 2025 13:06:02 GMT

The above query is not working with databricks