Conditional insert with multiple values

Question

This question builds on my previous question on conditional inserts. The problem I'm trying to solve now, is when I have multiple VALUES to be inserted using the same LAST_INSERT_ID (which is set to @EVENT_ID). With my previous C# script this is what the statement would look like:

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) 
VALUES (@EVENT_ID, 'SBEPN', '41420F'), (@EVENT_ID, 'Notes', 'Sent to USER2');

Using the same C# script modified to take advantage of the conditional insert provided in the linked question, this is what I end up with (if there's just a single set of VALUES it works as expected):

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) 
SELECT t.* FROM ( 
    SELECT @EVENT_ID, 'SBEPN', '41420F', @EVENT_ID, 'Notes', 'Sent to USER2') 
t 
WHERE @EVENT_ID IS NOT NULL;

The Problem: Running this as-is gets an "Error code 1241. Operand should contain 1 column(s)". Which makes sense, I'm providing six pieces of data where it's expecting three according to the table definition. I tried providing the data in two groups of three, separated with parenthesis trying to emulate the VALUES clause in the original script above, as well as various other things that I couldn't get to work:

SELECT t.* FROM (
SELECT (@EVENT_ID, 'SBEPN', '41420F'), (@EVENT_ID, 'Notes', 'Sent to USER2')) 
t 
WHERE @EVENT_ID IS NOT NULL;

It's possible to have as many as half a dozen of these follow-on clauses, and they all need to use the same LAST_INSERT_ID (set as variable @EVENT_ID) so they're associated with a single parent row.

Workaround: I know that I can just do multiple inserts of the data using single groups of three, EG:

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) 
SELECT t.* FROM ( 
    SELECT @EVENT_ID, 'SBEPN', '41420F') 
t 
WHERE @EVENT_ID IS NOT NULL;

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE)     
SELECT t.* FROM (
    SELECT @EVENT_ID, 'Notes', 'Sent to USER2')
t 
WHERE @EVENT_ID IS NOT NULL;

Question: Can I do this in a single insert statement that emulates the results from the first example where there are multiple VALUES statements?

score 1 · Accepted Answer · answered Sep 19 '14 at 16:03

1

Is this what you want?

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) 
     SELECT t.*
     FROM (SELECT @EVENT_ID as e, 'SBEPN', '41420F' UNION ALL
           SELECT @EVENT_ID as e, 'Notes', 'Sent to USER2'
          ) t
     WHERE e IS NOT NULL;

answered Sep 19 '14 at 16:03

Gordon Linoff

1,242,037
58
646
786

That works great, and if I have to add more clauses, I can just tack on another UNION ALL per clause (already tested to work as expected). Thanks a lot, I appreciate the help. – delliottg Sep 19 '14 at 17:08

Conditional insert with multiple values

1 Answers1