Re: INSERT by subquery behavior

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/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)