Sql-server – Multiple inserts using select row and variable combination with one SQL statement

insertsql serversql-server-2008

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

Best Answer

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

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

Related Question