[SOLVED] Combine INSERT INTO and SELECT for multiple VALUES

Combine INSERT INTO and SELECT for multiple VALUES

I know that the most common way for inserting multiple new rows into a table is:

INSERT INTO fruits (fruit, colorId)
VALUES ('apple', 1),
       ('orange', 2),
       ('strawberry', 3);

I also know that I can insert results obtained from a SELECT, even using custom values:

INSERT INTO fruits (fruit, colorId)
SELECT 'pear', id
FROM colors
WHERE color = 'green';

The thing is that, using any of those options (or maybe a different one, which I do not know), I would like to insert multiple values using the result obtained in such a query. For instance:

SELECT id
FROM colors
WHERE color = 'yellow';

would return a single value 4 (the id for yellow), which I would like to reuse in a multiple insert query such as

INSERT INTO fruits (fruit, colorId)
VALUES ('banana', id),
       ('lemon', id);

(where id should be a 4). Is it possible?

EDIT: By the way, I would also like to avoid using subqueries like

INSERT INTO fruits (fruit, colorId)
VALUES ('banana', (SELECT id FROM colors WHERE color = 'yellow')),
       ('lemon',  (SELECT id FROM colors WHERE color = 'yellow'));

Solution

BTW, you can do it like below:

DECLARE @Id INT
SET @Id = (SELECT id FROM colors WHERE color = 'yellow')
INSERT INTO fruits (fruit, colorId)
VALUES ('banana', @Id),
   ('lemon',  @Id);