Embedded select query?

What I'd like to do is use the result of one query as the input for
another. eg:

INSERT INTO foo VALUES (
(SELECT id FROM people WHERE name = "bar"),
10,
'foobar'
) WHERE
id = 1;

Problem is, MSSQL wants a scalar value. Is there a way around this?
Can someone tell me the correct syntax for what I want to do, or is this
something that will have to be done outside SQL?

Err. Hope I've been clear. Thanks for any help you folks can give.Don't use the VALUES clause if you are INSERTing values from a query. It's
also good practice always to specify the column names in an INSERT
statement.

INSERT INTO foo (col1, col2, col3)
SELECT id, 10, 'foobar'
FROM people
WHERE name = 'bar';

I'm not sure what the final WHERE clause in your code was meant to be so
I've left it out. You can't have a WHERE clause on an INSERT statement.

--
David Portas
SQL Server MVP
--|||Eek. It's been a long day I guess. There wasn't supposed to be a where
on that :) But you've answered my question none the less. Thanks.

FYI, I didn't bother with the column specifications for the sake of
simplicity.

Thanks all the same!

David Portas wrote:
> Don't use the VALUES clause if you are INSERTing values from a query. It's
> also good practice always to specify the column names in an INSERT
> statement.
> INSERT INTO foo (col1, col2, col3)
> SELECT id, 10, 'foobar'
> FROM people
> WHERE name = 'bar';
> I'm not sure what the final WHERE clause in your code was meant to be so
> I've left it out. You can't have a WHERE clause on an INSERT statement.