Embedded select query?

[DarkChiild]

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.

[David Portas]

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

[DarkChiild]

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.