Inserting multiple rows based on query results

Question

I am building out some Data Protection answers for an update to our website and this one has me stumped in SQL Server 2008 R2:

I need to insert 'answers' to these questions for legacy customers. Here's an example statement that works for a single record:

INSERT INTO DataProtection
(
    Id, 
    HolderCompanyId,
    IssuerCompanyid,
    AnswerEnum, --The answer (1,2,3) to the DP question
    AnsweredDate,
    ClientId, --This is the customer's unique ID
    QuestionId --The ID of the question from a different table
) 
VALUES 
(
    '9ee9455b-3ba5-440e-8329-c556ae8a1c7e',
    1127,
    1127,
    10,
    { ts '2015-01-20 00:00:00' },
    '12345',
    '3'
);

How would I backbone off of this to insert thousands of rows (many times several rows per ClientID) for records that do not have an entry for each question (QuestionID)?

Example: Customer ID 12345 has answered '1' for Data Protection questions 1 and 2, but does not have an entry in the database for questions 3-6. There are many records like this.

Does each row in the DataProtection table represent a question for a user? So, in the past you have only had 3 questions for some customers and now you have 6 and are somehow trying to add additional rows such that each customer has 6 question answers? — Sean Cox
– Sean Cox, Commented Jan 22, 2015 at 2:47
If this is the case could you not just LEFT JOIN between the DataProtection and Questions table based on the Question ID and then use COALESCE() to replace NULL values with a specific value if a legacy customer doesn't have an answer. It's kind of hard without seeing the db schema, but if this is what you're looking for let me know and I'll right up an answer. — Sean Cox
– Sean Cox, Commented Jan 22, 2015 at 2:52
Yes, @TheShaman I think That's exactly what I'm looking for! — StephenYo
– StephenYo, Commented Jan 22, 2015 at 14:55

Ryan · Accepted Answer · 2015-01-22 02:45:45Z

1

I'm not clear if you have the records to insert ('elsewhere') or already in a similar table in your database.

To do multiple updates in one statement use this:

INSERT INTO 
    DataProtection (Id, HolderCompanyId ...)
VALUES 
    ('9ee9455b-3ba5-440e-8329-c556ae8a1c7e', 1127 ...),
    ('9ee9455b-3ba5-440e-8329-c556ae8a1c7f', 1128 ...),
    ('9ee9455b-3ba5-440e-8329-c556ae8a1c7g', 1129 ...),
    ('9ee9455b-3ba5-440e-8329-c556ae8a1c7h', 1120 ...)

If you have the data in another table, you can select it into another table using this syntax:

INSERT INTO Table (DestCol1, DestCol2)
SELECT SourceCol1, SourceCol2
FROM SourceTable
WHERE SomeCondition

answered Jan 22, 2015 at 2:45

Ryan

4,0127 gold badges48 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sean Cox · Accepted Answer · 2015-01-22 03:12:47Z

0

I don't have SQL Server pulled up to test but something like this:

    INSERT INTO DataProtection
(SELECT Id, HolderCompanyId, IssuerCompanyid, COALESCE(AnswerEnum, 'Answer Not Available'), COALESCE(AnsweredDate, 'No Answer Date')
    ClientId, cj.questionId
 FROM 
(SELECT UNIQUE ClientId FROM DataProtection 
CROSS JOIN QuestionsTable) AS cj
LEFT JOIN DataProtection AS dp ON cj.QuestionID=dp.QuestionId
WHERE AnswerEnum IS NULL)

You essentially want to CROSS JOIN the unique client Ids with the question Ids and then JOIN it with the DataProtection table. Then filter to get the rows which do not have answers. These are the ones you want to insert back into the DataProtection table.

answered Jan 22, 2015 at 3:12

Sean Cox

7821 gold badge5 silver badges12 bronze badges

Collectives™ on Stack Overflow

Inserting multiple rows based on query results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related