SEO

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>?

2. Can I still do this statement if the indexes and column
*constraints* are different in <table2>? The columns themselves have
the same size and datatypes but there would likely be additional
indexes but fewer constraints on <table2>

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

John C. Sullivan wrote:
> 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>?
>
> 2. Can I still do this statement if the indexes and column
> *constraints* are different in <table2>? The columns themselves have
> the same size and datatypes but there would likely be additional
> indexes but fewer constraints on <table2>
>
> 3. Can the MERGE statement be used with a query to update multiple
> rows and would this eliminate the issues above?

1. SELECT * is SELECT * is SELECT *. The behavior doesn't change because
yo are inserting into a table. If you want distinct rows then ask for
them: SELECT DISTINCT ...

2. Indexes are irrelevant constraints are not. A constraint violation is
a constraint violation is a constraint violation. Why would you expect
behavior to be different?

3. Irrelevant. You can not write SQL to violate the rules nor should you
want to do so.

I sincerely hope you are a student. Because if thoughts such as these
are percolating around your brain and you are on someone's payroll you
are a danger to yourself and others. Please ask for advice from someone
in a senior/management position and do something about enrolling in a
good education program.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

> 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.

1. I know what SELECT * is and telling me that "Select * is Select *
is Select * "(while correct, congratulations) tells me me nothing and
really didn't address what I was asking. What I wished to know in
effect, and what the next responder told me, is that the INSERT is
treated as a transaction so that if one fails they all rollback. This
is what I suspected but I wanted confirmation before I abandoned the
thought entirely.

2. Once again, telling me "A = A" tells me nothing (Are your students
at UW impressed with this?). The Oracle book I was reading was not
clear on indexes and I wanted to know if there would be a problem if a
constraint didn't exist on the second (destination) table that did
exist on the first (again the book I was reading was not entirely
clear). I wasn't planning on violating any constraints but the
documentation regarding INSERT of multiple records from one table into
another table using a query said the columns needed to be identical.
Does "identical" mean including constraints or just the names,
datatypes and sizes?

3. The example I was looking at regarding Oracle's MERGE statement did
not make it clear to me if I could use it with multiple records from a
query as opposed to a single record (like I could do with INSERT OR
UPDATE).

What it comes down to is I was trying to determine the simplest way to
transfer records from one table to another that have identical columns
but different constraints (the destination table has fewer) and
indexes (the destination table may have more). If the record exists
in the destination table, it will be the same as what's in the
originating table so I don't care if I overwrite an existing record or
if it's skipped as long as the new ones are added. If the database I
was using were MySQL, this would be very easily accomplished with a
REPLACE statement but Oracle, which I am forced by the circumstances
to be using, does not appear have a REPLACE statement. I was looking
for alternatives that would avoid multiple SQL statements and/or
program loops. I'm coming to the conclusion this is likely not
possible with Oracle.

Perhaps my questions did not make this clear enough but the proper
response from you as (supposedly) an educator would be to ask for more
information, not try to shock me into embarrassed silence. Your
students at UW won't learn much if they don't feel they can ask a
question without being embarrassed.

>
> 1. SELECT * is SELECT * is SELECT *. The behavior doesn't change because
> yo are inserting into a table. If you want distinct rows then ask for
> them: SELECT DISTINCT ...
>
> 2. Indexes are irrelevant constraints are not. A constraint violation is
> a constraint violation is a constraint violation. Why would you expect
> behavior to be different?
>
> 3. Irrelevant. You can not write SQL to violate the rules nor should you
> want to do so.
>
> I sincerely hope you are a student. Because if thoughts such as these
> are percolating around your brain and you are on someone's payroll you
> are a danger to yourself and others. Please ask for advice from someone
> in a senior/management position and do something about enrolling in a
> good education program.

Thank you for your help.