0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.