Re: INSERT by subquery behavior

> If you use an INSERT INTO <table1> SELECT * FROM <other table2 with
> same fields>, what is the behavior for the following situations:
>
> > 1. Some (but not all) of the selected records in <table2> have
> > duplicates in <table1>? Does the entire statement fail or only for
> > rows with duplicates in <table1>?

Its quite simple, all record are fetched to the table from select clause. If any of record violate constraint rule, full transaction is rolled back. Try use another technique, described in thread 'Oracle for Fun', like inserting row by row in a loop, with exception clause. Oracle would help you insert rows faster, but you need to check data you're inserting...

> > 3. Can the MERGE statement be used with a query to update multiple
> > rows and would this eliminate the issues above?

Whatever you SELECT, would be INSERTED, So if better SELECT then less errors occurs.

--
Noel

PS. An example: (plus80 session).

SET serveroutput ON
create table tsource
(
id number,
val varchar2(4)
);

insert into tsource values(1,'AAA');
insert into tsource values(1,'ABC');

create table tdestination
(
id number,
val varchar2(4)
);

alter table tdestination add constraint pk_tdest PRIMARY KEY(id);

insert into tdestination select * from tsource; --  it fails...
select * from tdestination; -- no rows will be selected.

begin
 for i IN
  ( select rownum, t.* from tsource t) loop
   begin
    insert into tdestination(id,val) values(i.id,i.val);
   exception
    when dup_val_on_index then
    DBMS_OUTPUT.Put_Line('Row No: '||i.rownum||' rejected');
   end;
  end loop;
 end;
/

select * from tdestination; -- one row was copied, one rejected.