cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Add Identity Column to Existing Table

jasperputs
New Contributor III

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:

image

1 ACCEPTED SOLUTION

Accepted Solutions

jasperputs
New Contributor III

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.

View solution in original post

6 REPLIES 6

-werners-
Esteemed Contributor III

At the moment that is not supported.

The alter table command only allows for sync identity.

Kaniz
Community Manager
Community Manager

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
New Contributor III

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
Community Manager
Community Manager

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
Contributor II

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.

the recent syntax is :

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

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!