SQL - Insert into table if doesn't have data

Question

I'm using MYSQL script and I want to insert some values in a table.

INSERT INTO `testType` (`name`) VALUES ('URL');
INSERT INTO `testType` (`name`) VALUES ('xD');
INSERT INTO `testType` (`name`) VALUES ('LOL');

But I only want to insert if the table is empty in the first place.

I'm not a SQL guy but basically

if(testType.length() == 0 {

    INSERT INTO `testType` (`name`) VALUES ('URL');
    INSERT INTO `testType` (`name`) VALUES ('xD');
    INSERT INTO `testType` (`name`) VALUES ('LOL');

}

How can I do this the simplest and smallest way possible? Thank you.

EDIT: my question is different. I want to insert ALL THE DATA if the table is empty. not only one insert at the time

Possible duplicate of [Execute INSERT if table is empty?](https://stackoverflow.com/questions/5307164/execute-insert-if-table-is-empty) — Kasia Gogolek, Apr 20 '18 at 10:37
Possible duplicate of [MYSQL - Insert If Table is empty](https://stackoverflow.com/questions/49941928/mysql-insert-if-table-is-empty) — Vatev, Apr 20 '18 at 13:19

score 1 · Answer 1 · answered Apr 20 '18 at 10:38

1

First, I would suggest doing this in one step:

INSERT INTO testType(name)
    VALUES ('URL'), ('xD'), ('LOL');

Then, you can express this without IF:

INSERT INTO testType(name)
    SELECT t.name
    FROM (SELECT 'URL' as name UNION ALL
          SELECT 'xD' as name UNION ALL
          SELECT 'LOL' as name
         ) t
    WHERE NOT EXISTS (SELECT 1 FROM testType);

Finally, if you want to insert these values if each doesn't exist, then you can let the database do the work. First, define a unique constraint/index on the name (if name is not already the primary key), and then use ON DUPLICATE KEY UPDATE:

CREATE UNIQUE INDEX unq_testtable_name ON testtable(name);

INSERT INTO testType(name)
    VALUES ('URL'), ('xD'), ('LOL')
    ON DUPLICATE KEY UPDATE name = VALUES(name);

answered Apr 20 '18 at 10:38

Gordon Linoff

1,122,135
50
484
624

this add stuff to the database even tho the table is not empty. – Apr 20 '18 at 13:56
i only want to add that insert IF the table has 0 rows – Apr 20 '18 at 13:59
@user9674353 . . . The second query does just that. The last makes sure that the three values that you want are all in the table. – Gordon Linoff Apr 20 '18 at 18:29

DineshDB · Answer 2 · 2018-04-20T11:28:56.517

0

Try this:

DECLARE @ExistCount INT;
SET @ExistCount = (SELECT COUNT(1) FROM `testType`);

IF @ExistCount<1
THEN
    INSERT INTO `testType` (`name`) VALUES ('URL'),('xD'),('LOL');
END IF;

edited Apr 20 '18 at 11:28

answered Apr 20 '18 at 10:35

DineshDB

5,240
4
28
42

** or DELIMITER expected, got IF** – Apr 20 '18 at 10:37

score 0 · Answer 3 · answered Apr 20 '18 at 10:40

You can use stored procedure

DELIMITER //

CREATE PROCEDURE `proc1` ()
BEGIN

        SELECT COUNT(*) INTO variable1 FROM testType;
        IF variable1 = 0 THEN
            INSERT INTO `testType` (`name`) VALUES ('URL');
            INSERT INTO `testType` (`name`) VALUES ('xD');
            INSERT INTO `testType` (`name`) VALUES ('LOL');
    END WHILE;
END //

SQL - Insert into table if doesn't have data

3 Answers3