CODEWITHSUNDEEP
sqlsql-serversql-server-2008
StephenYo
StephenYo

Reputation: 69

Inserting multiple rows based on query results

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.

Upvotes: 0

Views: 888

Answers (2)

Sean Cox
Sean Cox

Reputation: 782

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.

Upvotes: 0

Ryan
Ryan

Reputation: 3982

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

Upvotes: 1

Related Questions