topic Re: insert into a table with an identity column fails in Data Engineering

insert into a table with an identity column fails

nbakh — Thu, 06 Oct 2022 15:53:57 GMT

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)

Re: insert into a table with an identity column fails

Hubert-Dudek — Fri, 14 Oct 2022 12:27:37 GMT

instead of using

GENERATED ALWAYS AS IDENTITY,

you need to use

GENERATED BY DEFAULT AS IDENTITY

Re: insert into a table with an identity column fails

Debayan — Sat, 08 Oct 2022 05:16:17 GMT

Hi, Looks like it is not supported.

Please refer: https://github.com/delta-io/delta/issues/1215

Re: insert into a table with an identity column fails

karan_singh — Fri, 10 Nov 2023 06:22:55 GMT

Hi,

Specify insert columns as below

%sql
INSERT INTO demo_test (product_type, sales)
SELECT product_type, sales FROM demo