CODEWITHSUNDEEP
mysql
delliottg
delliottg

Reputation: 4140

Conditional insert with multiple values

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?

Upvotes: 0

Views: 917

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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;

Upvotes: 1

Related Questions