MySQL insert array of hardcoded values

Question

I have a list of integers and I would like an INSERT that adds all of them as a specific column. The idea is that I can avoid using multiple INSERT or I can avoid using an SQL FOR LOOP.

Here's what I tried:

INSERT INTO my_table
(id, number, text)
SELECT array_id, 5, 'Text'
WHERE array_id = (SELECT 9,14,19,39,58,15,1,59,40,20,17,69,12,42,22,2,57,1,8,4)

OR

WHERE array_id IN (9,14,19,39,58,15,1,59,40,20,17,69,12,42,22,2,57,1,8,4)

And various other things,

Is there a quick way to do that?

PART B of the same question is to add combinations of Lists Like this:

 INSERT INTO my_table
    (id, number, text)
    SELECT array_id, array_number, array_text
    WHERE array_id = (SELECT 9,14,19,39,58,15,1,59,40,20,17,69,12,42,22,2,57,1,8,4)
    AND array_number = (SELECT 1,2,3)
    AND array_text = (SELECT 'Text A', 'Text B')

So that it creates all possible combinations of those.

If such super script exists I definitely want to know it!

EDIT:

I Think my question is similar to this here.

Because I have a very long list of ids and it would take me forever. — Nick
– Nick, Commented May 22, 2014 at 15:57

Nick · Accepted Answer · 2014-05-22 16:11:10Z

I found a very promising solution which is:

 INSERT INTO my_table
    (id, number, text)
    SELECT array_id, array_number, array_text
   FROM (SELECT 9 AS array_id UNION
          SELECT 14 UNION
          SELECT 19 UNION
          SELECT 39 UNION
          SELECT 58 UNION
          SELECT 15 UNION
          SELECT 1 UNION
          SELECT 59 UNION
          SELECT 40 UNION
          SELECT 20 UNION
          SELECT 17 UNION
          SELECT 69 UNION
          SELECT 12 UNION
          SELECT 42 UNION
          SELECT 22 UNION
          SELECT 2 UNION
          SELECT 57 UNION
          SELECT 1 UNION
          SELECT 8 UNION
          SELECT 4 ) xx
    CROSS JOIN (SELECT 1 AS array_number 
                  UNION SELECT 2
                  UNION SELECT 3
                          ) yy
    CROSS JOIN (SELECT 'Text A' AS array_text UNION 
                          SELECT 'Text B' UNION 
                          SELECT 'Text C') zz

There is this similar example that I drew inspiration from but mine contains more combitnations. I run it at sql fiddle and it seems to work.

Collectives™ on Stack Overflow

MySQL insert array of hardcoded values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related