10-06-2022 08:53 AM
i am trying to insert into a table with an identity column using a select query.
However, if i include the identity column or ignore the identity column in my insert it throws errors. Is thee a way to insert into select * from a table if the insert table has an identity column?
%sql
CREATE OR REPLACE TABLE demo (
id BIGINT GENERATED ALWAYS AS IDENTITY,
product_type STRING,
sales BIGINT
);
%sql
insert into demo_test
SELECT id,product_type, sales from demo
AnalysisException: Providing values for GENERATED ALWAYS AS IDENTITY column id is not supported.
%sql
insert into demo_test
SELECT product_type, sales from demo
nalysisException: Cannot write to 'spark_catalog.default.demo_test', not enough data columns; target table has 3 column(s) but the inserted data has 2 column(s)
10-07-2022 10:16 PM
Hi, Looks like it is not supported.
Please refer: https://github.com/delta-io/delta/issues/1215
10-10-2022 12:48 AM
Hi @nauman bakhrani, We haven’t heard from you since the last response from @Debayan Mukherjee , and I was checking back to see if you have a resolution yet.
If you have any solution, please share it with the community, as it can be helpful to others. Otherwise, we will respond with more details and try to help.
Also, Please don't forget to click on the "Select As Best" button whenever the information provided helps resolve your question.
10-14-2022 05:27 AM
instead of using
GENERATED ALWAYS AS IDENTITY,
you need to use
GENERATED BY DEFAULT AS IDENTITY
11-09-2023 10:22 PM
Hi,
Specify insert columns as below
%sql
INSERT INTO demo_test (product_type, sales)
SELECT product_type, sales FROM demo
Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections.
Click here to register and join today!
Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.