Can insert into table * number with same data in Mysql?-CodePudding

Can insert into table * number with same data in Mysql?(Yes, have same value 3 or 100 times)

 INSERT INTO `test`(`name`, `test1`, `other_status`, `status`) 
 VALUES ('string', 'KPrBf9', 1, 0) * 3;

CodePudding user response：

You need a stored procedure to insert same statement multiple times:

    DELIMITER $$
CREATE PROCEDURE insert_loop ( IN nr_input bigint)
BEGIN
  DECLARE counter BIGINT DEFAULT 0;

  my_loop: LOOP
    SET counter=counter 1;

    IF counter=nr_input THEN
      LEAVE my_loop;
    END IF;

    INSERT INTO `test`(`name`, `test1`, `other_status`, `status`) 
    VALUES ('string', 'KPrBf9', 1, 0);

  END LOOP my_loop;
END$$
DELIMITER ;

Above procedure will insert same values based on the input parameter you give.

Call the procedure by using:

call insert_loop(5); ---number of rows to be inserted

CodePudding user response：

Not sure what do you mean by *3 But if you want to insert multiple records you can use the following

INSERT INTO `test`(`name`, `test1`, `other_status`, `status`) 
 VALUES ('string', 'KPrBf9', 1, 0),
('string', 'KPrBf9', 1, 0),
('string', 'KPrBf9', 1, 0);

CodePudding user response：

Forget about INSERT .. VALUES and study INSERT .. SELECT .

INSERT INTO table_name (columns_names)  -- (`name`, `test1`, `other_status`, `status`)
WITH RECURSIVE cte AS (
    SELECT 1 AS amount
    UNION ALL
    SELECT amount   1 FROM cte WHERE amount < @amount_of_rows_to_insert
)
SELECT 'values list'   -- 'string' AS name, 'KPrBf9' AS test1, 1 AS other_status, 0 AS status
FROM cte;

If MySQL version is too old and does not support CTE then

INSERT INTO table_name (columns_names)
SELECT 'values list'
FROM ( SELECT 1 AS amount UNION ALL
       SELECT 2 UNION ALL
       -- ...
       SELECT @amount_of_rows_to_insert ) AS cte;