Re: Inserting multiple rows wtih a SELECt in the values clause

>>>>> "stan" == stan  <stanb@panix.com> writes:

 stan> I suspect this may be because the SELECT in the values clause
 stan> returns multiple rows?

Understand this: VALUES is really just a special form of SELECT that
returns only the specific rows that you tell it to construct. Every
single row returned by a VALUES clause is separately constructed.

i.e. VALUES (...),(...);  will return exactly two rows regardless of
what is inside the (...). VALUES (...);  is always exactly one row.
And so on.

The general form of INSERT is actually:

INSERT INTO table(columns) <query>

where <query> is any valid query returning any number of rows. The use
of VALUES for the <query> is just a convenient shorthand for cases where
the exact number of rows to be inserted, and their content, is known in
advance.

So, if you're inserting some set of rows generated from a query, the
word VALUES should not appear in the top-level statement. What you want
is:

INSERT INTO rate(employee_key, project_key, work_type_key, rate)
SELECT employee.employee_key,
       project.project_key,
       work_type.work_type_key,
       1 as rate
  FROM employee 
 CROSS JOIN project 
 CROSS JOIN work_type;

-- 
Andrew (irc:RhodiumToad)