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)