Load data from a Table that has the same structure as Table1 with the only diferrence of an ID not null column?

Question

So, I know that if you want to load data into a table that does not have the same structure as the data, well you have to do it in the way of column by column, the thing that in this situation, I have many tables created exactly with the only diferrence than table2's have an indentity column. So my questions is, is there a way that I can load data with the db2 INSERT INTO table2 SELECT * FROM table1 but skipping this identity column? somehow in the sytax that I can tell that to the database? doing it column by column syntax will take a lot of time.

Answer 1

You could make the identity column IMPLICITLY HIDDEN.

CREATE TABLE T(I INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, A INT)

ALTER TABLE T ALTER COLUMN I SET IMPLICITLY HIDDEN

INSERT INTO T VALUES (42)

SELECT I, A FROM T 

will output

I|A     
-|------
1|    42

Answer 2

If the source and target tables do not have exactly the same columns, created in the same order, you cannot avoid specifying what columns to insert where, one way or another.

You can either specify them in the insert statement, excluding the identity column:

insert into target (col1, col2) select * from source

or, if you run such inserts regularly, you can save yourself some future hassle by defining a view based on the target table once:

create view v_target as select col1, col2 from target;
insert into v_target select * from source;

fiddle link

Answer 3

You can generate the columns like:

select listagg(colname,', ') as cols within group (order by colno) 
from syscat.columns 
where identity = 'N' 
and tabschema = ? 
and tabname = ?

Now put cols in both the select and the insert. The:

within group (order by colno)

is not necessary, but I consider it nice if the columns are generated in that order.