Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    115

    Unanswered: Skip records that violate constraints and continue load

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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);
    Last edited by Marcus_A; 05-23-06 at 04:52.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    INSERT will not work.

    consider using the LOAD-utility with ENFORCE CONSTRAINTS option.

  4. #4
    Join Date
    Jun 2004
    Posts
    115
    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

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2hrishy
    I was just wundering if there was nay way to do this using sql/pl or something ?
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •