CODEWITHSUNDEEP
mysqlsqlspring-booth2mybatis
Subham
Subham

Reputation: 29

Insert multiple records if table is empty

I have a scenario where I have to insert multiple records if the table is empty. My sql script runs everytime I run my server. I don't use H2 database so that I can insert on duplicate key. I use myBatis. I want the query to insert only if the table is empty.

My insert query is as follows:

LOCK TABLES `t002_seat_mst` WRITE;
INSERT INTO `t002_seat_mst` VALUES (2020,1,1,0,0,1,0,'228A'),(2021,1,2,0,0,1,0,'229A'),
    (2022,1,3,0,0,1,0,'231A'),(2023,1,4,0,0,1,0,'233A'),(2024,1,5,0,0,1,0,'235A'),
    (2025,1,6,0,0,1,0,'237A'),(2026,1,7,0,0,1,0,'239A'),(2027,1,8,0,0,1,0,'240A'),
    (2028,1,9,0,0,1,0,'242A'),(2029,1,10,0,0,1,0,'244A'),(2030,1,11,0,0,1,0,'246A');
UNLOCK TABLES;

How can I achieve this?

Upvotes: 0

Views: 904

Answers (1)

Akina
Akina

Reputation: 42844

Test

LOCK TABLES `t002_seat_mst` AS t1 WRITE, `t002_seat_mst` AS t2 READ;

INSERT INTO t002_seat_mst AS t1
SELECT * 
FROM ( SELECT 2020 a,1 b,1 c,0 d,0 e,1 f,0 g,'228A' h UNION ALL
       SELECT 2021,1,2,0,0,1,0,'229A' UNION ALL
       SELECT 2022,1,3,0,0,1,0,'231A' UNION ALL
       SELECT 2023,1,4,0,0,1,0,'233A' UNION ALL
       SELECT 2024,1,5,0,0,1,0,'235A' UNION ALL
       SELECT 2025,1,6,0,0,1,0,'237A' UNION ALL
       SELECT 2026,1,7,0,0,1,0,'239A' UNION ALL
       SELECT 2027,1,8,0,0,1,0,'240A' UNION ALL
       SELECT 2028,1,9,0,0,1,0,'242A' UNION ALL
       SELECT 2029,1,10,0,0,1,0,'244A' UNION ALL
       SELECT 2030,1,11,0,0,1,0,'246A' ) data
WHERE NOT EXISTS ( SELECT NULL
                   FROM t002_seat_mst AS t2 );

UNLOCK TABLES;

Upvotes: 3

Related Questions