Hi

I need to populate one table from another.But the second table has constraints

i am doing it like like this

insert into table t2
select * from tabl t1

the problem is i want the insert to continue if there are records in t1 that will violate the constraints in t2.

The records that do not match the constraints should be ignored.My question is how do we do it in db2 ?

regards
db2hrishy

You can EXPORT the data from the first table, and then LOAD it to the second table. All the rows will be loaded, but the table will be in check pending state. When you run the SET INTEGRITY statement, you use the option to discard the rows with RI errors.

The EXPORT and LOAD commands are documented in the Command Reference manual.

Another option is use a subquery to validate the foreign keys:

insert into table t2
select * from tabl t1 where exists (subquery that checks foreign key);

INSERT will not work.

consider using the LOAD-utility with ENFORCE CONSTRAINTS option.

Hi Folks

Thank you very much.

I was just wundering if there was nay way to do this using sql/pl or something ?

regards
Hrishy

Sure: just insert the rows one by one and ignore the negative SQLcodes on the way.
The simplest way to do this would be with a cursor to iterate the rows returned by your SELECT.
Nevertheless, certainly when the number of rows is large, the LOAD solution is in far the better one.