CODEWITHSUNDEEP
mysql
tdoakiiii
tdoakiiii

Reputation: 372

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

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).

Upvotes: 0

Views: 510

Answers (2)

Uueerdo
Uueerdo

Reputation: 15961

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`;

Upvotes: 1

Alex
Alex

Reputation: 17289

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

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

Upvotes: 1

Related Questions