How to insert multiple rows into MySQL

Question

I am new to MySQL (have just been using SQL Server) and I am wondering how to create multiple rows easily!

I am looking to create about 300 rows, so the methods I have found online just don't seem practical enough..

When using SQL Server, you can just use a GO statement, and then enter a number afterwards and it will run the command that many times, such as : GO 50

Is there anything as simple as this on MySQL? I have read about using a loop statement, but I cannot find any info on it?

Any help will be really appreciated!

Thanks,

-Liam.

Answer 1

You may prepare you data with Excel, export as CSV and import to DB by common Database Tools like DBeaver and Sql Squirrel

https://github.com/dbeaver/dbeaver/issues/722

Answer 2

You could create a Repeat statement:

DELIMITER //

CREATE FUNCTION CalcularSueldo ( starting_value INT )
RETURNS INT

BEGIN

   DECLARE sueldo INT;

   SET sueldo = 0;

   label1: REPEAT
     SET sueldo = sueldo + starting_value;
   UNTIL sueldo >= 10000000 
   END REPEAT label1;

   RETURN sueldo;

END; //

DELIMITER ;

It's basically a loop, where you put the condition using UNTIL. Here it is used in a function but you can adapt it to your needs.

Answer 3

Without using loop:

CREATE TABLE noderedtest(Password TEXT, Email TEXT);

INSERT INTO noderedtest (Password, Email)  -- storing password in clear text
                                           -- is very bad idea
WITH RECURSIVE cte AS
(
   SELECT 1 AS i
   UNION ALL
   SELECT i+1
   FROM cte
   WHERE i < 300
)
SELECT 'Test', '[e-mail]'
FROM cte;

SELECT * FROM noderedtest;

DBFiddle Demo MySQL 8.0+