MySQL insert multiple rows into empty table using SELECT FROM DUAL WHERE NOT EXISTS

Question

I am using this code to insert a default row if the table is definitely empty. I am trying to extend this to insert multiple rows but cannot figure out the syntax:

INSERT INTO myTable(`myCol`)
SELECT 'myVal'
FROM DUAL
WHERE NOT EXISTS (SELECT * FROM myTable);

What i am getting (@Uueerdo)

CREATE TEMPORARY TABLE `myDefaults` ( name VARCHAR(100) NULL DEFAULT NULL);# MySQL returned an empty result set (i.e. zero rows).

INSERT INTO myDefaults (name) VALUES ('a'), ('b');# 2 rows affected.


SET @valCount := 0;# MySQL returned an empty result set (i.e. zero rows).

SELECT COUNT(1) INTO @valCount FROM blsf;# 1 row affected.


INSERT INTO blsf(name)
SELECT name 
FROM myDefaults 
WHERE @valCount > 0;# MySQL returned an empty result set (i.e. zero rows).


DROP TEMPORARY TABLE `myDefaults`;# MySQL returned an empty result set (i.e. zero rows).

I am fairly certain MySQL does not supply a way to insert multiple values with such an insert (I believe that insert would work without DUAL). Why not check myTable once, with "SELECT 1 FROM myTable LIMIT 1", and then run a standard INSERT VALUES query if you didn't get a result? — Uueerdo, Apr 29 '15 at 18:03
How can I use the result if the SELECT? I am not using any language other than SQL. Is it possible to use an inline IF statement of some sort? Or do I have to create a procedure? — tdoakiiii, Apr 29 '15 at 18:07
You'd have to use a stored procedure, or you could maybe insert the all default rows into a separate (even TEMPORARY) table... see answer below shortly. — Uueerdo, Apr 29 '15 at 18:09
possible duplicate of [MySQL IF statement to check if table is empty](http://stackoverflow.com/questions/29945590/mysql-if-statement-to-check-if-table-is-empty) — Alex, Apr 29 '15 at 18:32

Uueerdo · Accepted Answer · 2015-04-29T18:37:05.167

1

Something like this should work:

CREATE TEMPORARY TABLE `myDefaults` ( the_value INT|VARCHAR|whatever... )
;
INSERT INTO myDefaults (the_value) VALUES (myVal1), (myVal2), ....
;

SET @valCount := 0; -- Because I am paranoid ;)
SELECT COUNT(1) INTO @valCount FROM myTable;

INSERT INTO myTable(myCol)
SELECT the_value 
FROM myDefaults 
WHERE @valCount = 0
;

DROP TEMPORARY TABLE `myDefaults`;

edited Apr 29 '15 at 18:37

answered Apr 29 '15 at 18:13

Uueerdo

15,723
1
16
21

I'm trying but something is wrong. Please see my question for what I get when I enter this into my SQL as it's too complex to post here. – tdoakiiii Apr 29 '15 at 18:30
@tdoakiii, none of those are errors. If you want to see what is in the table afterward, just add a "SELECT * FROM myTable;" at the end. – Uueerdo Apr 29 '15 at 18:34
I know they aren't errors. The issue is nothing was added to the empty table. – tdoakiiii Apr 29 '15 at 18:35
1

@tdoakiiii, Oh! I messed up that last check, correcting above answer. To point it out, the WHERE should've used '=' not '>'. – Uueerdo Apr 29 '15 at 18:36

score 1 · Answer 2 · answered Apr 29 '15 at 18:32

1

http://sqlfiddle.com/#!2/ba9ed/1

INSERT INTO table1 (myColumn)
  SELECT 
    'myValue' 
  FROM (
    SELECT COUNT(*) c 
    FROM table1 t
    HAVING c=0) t2;

answered Apr 29 '15 at 18:32

Alex

16,739
1
28
51

MySQL insert multiple rows into empty table using SELECT FROM DUAL WHERE NOT EXISTS

2 Answers2