>>>>> "stan" == stan <stanb@xxxxxxxxx> 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)
