Multiple inserts using select row and variable combination with one SQL statement

Question

I'm working with SQL 2008 R2 and have a need to find a specific row in a table that has three fields and then insert multiple rows into the same table with each insert using two values from the found row but the third value is a variable that is different on each insert. Can this be done? Here is a non-functioning SQL statement that, I hope, shows what I am trying to do:

INSERT INTO Routings_Doors_Options
 (HeaderID, Option2, Option1) 
VALUES (Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA1'), 
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA2'), 
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA3'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA4'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA6'), 
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA7'),
(Routings_Doors_Options_1.HeaderID, Routings_Doors_Options_1.Option2, 'OGGA8')
SELECT HeaderID, Option2
FROM Routings_Doors_Options AS Routings_Doors_Options_1
WHERE (Option1 = 'OGGA')

Answer 1

You'll need to create a temporary table with the new variables, then cross apply them to the existing row.

I've set up an MCVE ¹:

CREATE TABLE dbo.rdo
(
 HeaderID int NOT NULL
 , Option2 varchar(10) NOT NULL
 , Option1 varchar(10) NOT NULL
);
INSERT INTO dbo.rdo (HeaderID, Option2, Option1)
VALUES (1, 'test', 'OGGA')

This code will insert the extra 8 variables into the table:

CREATE TABLE #variables
(
 Option1 varchar(10) NOT NULL
);
INSERT INTO #variables (Option1)
VALUES ('OGGA1')
 , ('OGGA2')
 , ('OGGA3')
 , ('OGGA4')
 , ('OGGA5')
 , ('OGGA6')
 , ('OGGA7')
 , ('OGGA8');
INSERT INTO dbo.rdo (HeaderID, Option2, Option1)
SELECT r.HeaderID
 , r.Option2
 , v.Option1
FROM dbo.rdo r
 CROSS APPLY #variables v
WHERE r.Option1 = 'OGGA';

This shows the table after the 8 rows have been added:

SELECT *
FROM dbo.rdo r

Results:

╔══════════╦═════════╦═════════╗
║ HeaderID ║ Option2 ║ Option1 ║
╠══════════╬═════════╬═════════╣
║ 1 ║ test ║ OGGA ║
║ 1 ║ test ║ OGGA1 ║
║ 1 ║ test ║ OGGA2 ║
║ 1 ║ test ║ OGGA3 ║
║ 1 ║ test ║ OGGA4 ║
║ 1 ║ test ║ OGGA5 ║
║ 1 ║ test ║ OGGA6 ║
║ 1 ║ test ║ OGGA7 ║
║ 1 ║ test ║ OGGA8 ║
╚══════════╩═════════╩═════════╝

---

^{1 - I own the referenced website}