db2 query insert from another table

问题:

I have a table product(id_product , name );

I have another one: productHistory (id_H , id_product , name);

I wanna create a query (db2) to insert all the rows of product in productHistory;

I have a sequence product_history_seq

I wanna do something like that:

insert into productHistory 
        (id_h ,  , id_product , name) 
  values ( product_history_seq.nextval,..

Or,

select (id_product , name) from product

What's the correct query?

回答1:

I believe you are looking for:

insert into  productHistory 
       ( id_h
       , id_product 
       , name
       ) 
  select next value for product_history_seq
       , id_product 
       , name 
    from product 
;

回答2:

Make id_h auto increment and try this

  insert into  productHistory ( id_product , name) values (select id_product , name from product );

id_h will auto-increment no need to put it in query

Hope it will help

回答3:

INSERT INTO productHistory (id_h, id_product, name)
  (SELECT
    product_history_seq.nextval,
    id_product,
    name
  FROM product);

That works

回答4:

"insert into yourtableone select default, val1, val2 from yourtabletwo" and declare the id as genereated by default