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

OK, now that figured out how to return the constant, this is the final
query I need to run.


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

But this fails.

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

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

>>>>> "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)