0

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.

1
  • Ok just to let you know, i solved this issue exporting the data, deleting the previously created identity column, loading the data back with the load command, (now having the same columns) and finally creating back again the identity column in that table.
    – Mike
    Commented Sep 29, 2020 at 22:24

3 Answers 3

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
0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.