CODEWITHSUNDEEP
mysql
crazy_php
crazy_php

Reputation: 23

How to generate mysql query to insert the 2000 records at a time?

Here is my scenario,

I have table 

ID(PK)  TYPE(PK)
01       3
02       3
03       3
04       3

now i have to insert ID upto 1000 with type 3... is there any option to do that automatically???

Upvotes: 1

Views: 1211

Answers (4)

Johan
Johan

Reputation: 76693

Use a stored procedure

DELIMITER $$

CREATE PROCEDURE insertMany(Pstart_id INT, Pend_id INT, Ptype INT)
BEGIN
  DECLARE i INT;
  IF (pstart_id > pend_id) THEN BEGIN
    SET i = PEnd_id;
    SET Pend_id = Pstart_id;
    SET Pstart_id = Pend_id;
  END; END IF;
  SET i = Pstart_id;
  WHILE I <= Pend_id DO BEGIN
    INSERT INTO table1 (id, `type`) VALUES (i, Ptype);
    SET i = i + 1;
  END; END WHILE;
END $$

DELIMITER ;

SELECT @first_id:= max(id)+1, @last_id:= max(id)+2001 FROM table1;
CALL InsertMany(@first_id, @last_id, 3);

This is faster than use a php loop because you are not sending a 1000 insert queries across the network.

Or you can have a look at this question: How to select a single row a 100 million x

Upvotes: 5

Ashwin A
Ashwin A

Reputation: 3867

Maybe you can try http://www.generatedata.com/#generator to insert automatically.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

If ID is a number:

INSERT INTO TableX
  ( ID, TYPE )
VALUES
  (1, 3) ,
  (2, 3) ,
  (3, 3) ,
  (4, 3) ,
  (5, 3) ,
  (6, 3) ;

INSERT INTO TableX
  ( ID, TYPE )
SELECT
    ID, 3
FROM
  ( SELECT
        7+ d0.ID - 1 + (d1.ID * 6) - 6 + (d2.ID * 36) - 36 + (d3.ID * 216) - 216
        AS ID
    FROM 
        TableX AS d0
      CROSS JOIN
        TableX AS d1
      CROSS JOIN
        TableX AS d2
      CROSS JOIN
        TableX AS d3
    WHERE d0.TYPE = 3 AND d0.ID <= 6
      AND d1.TYPE = 3 AND d1.ID <= 6
      AND d2.TYPE = 3 AND d2.ID <= 6
      AND d3.TYPE = 3 AND d3.ID <= 6 
  ) AS tmp
WHERE ID <= 1000 ;

Upvotes: 1

Bruce
Bruce

Reputation: 1542

Not sure about a MySQL query but since you are familiar with PHP why not write a very simple php script that loops 1000 times and does the insert..?

Upvotes: 0

Related Questions