SQL0803N duplicates and problems inserting

[gimme_this_gimme_that]

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_id,active,
created_by,created_date,
modified_by,modified_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.emp_id
join group_lookup gp on eg.group_id=gp.group_id
where
ee.office = '005901'
and gp.attribute='LOB'
and gp.group_name='SPSS';

[jefftyzzer]

On Mar 4, 3:09 pm, "[e-mail]"
<[e-mail]:

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_id,active,
created_by,created_date,
modified_by,modified_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.emp_id
join group_lookup gp on eg.group_id=gp.group_id
where
ee.office = '005901'
and gp.attribute='LOB'
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

[Mark A]

"jefftyzzer" <je********@sbcglobal.netwrote in message
news:1c**********************************@i12g2000 prf.googlegroups.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.

[gimme_this_gimme_that]

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.emp_id
join group_lookup gp on eg.group_id=gp.group_id
where
ee.office = '005901'
and ee.flag='NO'
and gp.attribute='LOB'
and gp.group_name='SPSS' ) as a
on a.group_id=emp_group.group_id
and a.emp_id=emp_group.emp_id
when not matched then
insert
(emp_id,group_id,active,
created_by,created_date)
values (a.emp_id,a.group_id,a.active,a.created_by,a.creat ed_date)
else ignore;

[Serge Rielau]

gi*******************@yahoo.com 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.emp_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