insert into a table with an identity column fails
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-07-2022 10:16 PM
Hi, Looks like it is not supported.
Please refer: https://github.com/delta-io/delta/issues/1215
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-14-2022 05:27 AM
instead of using
GENERATED ALWAYS AS IDENTITY,
you need to use
GENERATED BY DEFAULT AS IDENTITY
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/582998B45490C7019731A5B3A872C751/responsive_peak/images/icon_anonymous_message.png)