SQL0803N duplicates and problems inserting

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • gimme_this_gimme_that@yahoo.com

    SQL0803N duplicates and problems inserting

    Is there a way to tell DB2 to continue on error and then to insert
    tuples that don't exist in a table?

    I'd like to modify the following SQL statement to do that. Executing
    this statement results in a SQL0803N error.

    I'm using DB2 8.1.

    Thanks.

    insert into emp_group
    (emp_id,group_i d,active,
    created_by,crea ted_date,
    modified_by,mod ified_date)
    select ee.emp_id,
    (select group_id from group_lookup where short_name='BR( 005901) AT'),
    'YES',
    'BATCH01', current timestamp,
    'BATCH01', current timestamp
    from employee ee
    join emp_group eg on ee.emp_id=eg.em p_id
    join group_lookup gp on eg.group_id=gp. group_id
    where
    ee.office = '005901'
    and gp.attribute='L OB'
    and gp.group_name=' SPSS';

  • jefftyzzer

    #2
    Re: SQL0803N duplicates and problems inserting

    On Mar 4, 3:09 pm, "gimme_this_gim me_t...@yahoo.c om"
    <gimme_this_gim me_t...@yahoo.c omwrote:
    Is there a way to tell DB2 to continue on error and then to insert
    tuples that don't exist in a table?
    >
    I'd like to modify the following SQL statement to do that. Executing
    this statement results in a SQL0803N error.
    >
    I'm using DB2 8.1.
    >
    Thanks.
    >
    insert into emp_group
    (emp_id,group_i d,active,
    created_by,crea ted_date,
    modified_by,mod ified_date)
    select ee.emp_id,
    (select group_id from group_lookup where short_name='BR( 005901) AT'),
    'YES',
    'BATCH01', current timestamp,
    'BATCH01', current timestamp
    from employee ee
    join emp_group eg on ee.emp_id=eg.em p_id
    join group_lookup gp on eg.group_id=gp. group_id
    where
    ee.office = '005901'
    and gp.attribute='L OB'
    and gp.group_name=' SPSS';

    I think using the MERGE statement might be your best bet. In the WHEN
    NOT MATCHED clause you'd do the INSERT, otherwise (i.e., WHEN MATCHED)
    no-op.

    --Jeff

    Comment

    • Mark A

      #3
      Re: SQL0803N duplicates and problems inserting

      "jefftyzzer " <jefftyzzer@sbc global.netwrote in message
      news:1c3f59c3-9783-4ca5-b16b-d598fdc9e71f@i1 2g2000prf.googl egroups.com...
      I think using the MERGE statement might be your best bet. In the WHEN
      NOT MATCHED clause you'd do the INSERT, otherwise (i.e., WHEN MATCHED)
      no-op.
      >
      --Jeff
      Obviously, the PK and/or unique index would have to be removed.


      Comment

      • gimme_this_gimme_that@yahoo.com

        #4
        Re: SQL0803N duplicates and problems inserting

        Thanks Jeff,

        Merge works like a charm.

        merge into emp_group
        using (
        select ee.emp_id,
        (select group_id from group_lookup where short_name='$g_ short')
        group_id,
        'YES' active,
        'BATCH01' created_by, current timestamp created_date
        from employee ee
        join emp_group eg on ee.emp_id=eg.em p_id
        join group_lookup gp on eg.group_id=gp. group_id
        where
        ee.office = '005901'
        and ee.flag='NO'
        and gp.attribute='L OB'
        and gp.group_name=' SPSS' ) as a
        on a.group_id=emp_ group.group_id
        and a.emp_id=emp_gr oup.emp_id
        when not matched then
        insert
        (emp_id,group_i d,active,
        created_by,crea ted_date)
        values (a.emp_id,a.gro up_id,a.active, a.created_by,a. created_date)
        else ignore;

        Comment

        • Serge Rielau

          #5
          Re: SQL0803N duplicates and problems inserting

          gimme_this_gimm e_that@yahoo.co m wrote:
          Thanks Jeff,
          >
          Merge works like a charm.
          >
          merge into emp_group
          using (
          select ee.emp_id,
          (select group_id from group_lookup where short_name='$g_ short')
          group_id,
          'YES' active,
          'BATCH01' created_by, current timestamp created_date
          from employee ee
          join emp_group eg on ee.emp_id=eg.em p_id
          Without diving into the details in this query, the fact that you refer
          to the
          MERGE target here strikes me as very odd.

          Cheers
          Serge



          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab

          Comment

          Working...