- 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.
- 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?
- 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.
Received on Thu Feb 05 2004 - 15:01:41 CST