Use “LIMIT” in a MySQL “INSERT”?

Question

Can i use LIMIT 2 on MySQL INSERT query? e.g.

INSERT INTO MyTable
   (user_name,password)
VALUES
   (john,366543),
   (sam,654654)
LIMIT 2

I tried and its saying

`#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 2' at line 1`

Answer 1

Mostly If we are inserting data from another table that time we need to set limit to inserting specific numbers of data

     insert into cas_user (cas_name) select cas_name from users limit 1,5;

Hope this will help.

Answer 2

LIMIT 2 will only work with select no values

Answer 3

no you cannot use limit in insert query

Answer 4

You could do this using the INSERT ... SELECT syntax:

INSERT INTO MyTable (user_name, password)
SELECT 'john', '366543'
UNION ALL SELECT 'sam', '654654'
LIMIT 2;

Not sure why you would want to... maybe if you had a very long list of static values that you wanted to easily control by setting the limit?

EDIT: As pst notes, the LIMIT is actually part of the SELECT, and has nothing to do with the INSERT itself...

Answer 5

I know it's an old post but you can use foreach loop to limit insert statement. Something like:

    $i = '1';   
    foreach($item as $item){
        IF($i <= '2'){
            IF ($stmt = $connection->prepare("INSERT IGNORE INTO `db`.`table`( `item`) VALUES (?)")) {

                /* Bind our params */
                $stmt->bind_param('s' , $item);

                $stmt->execute();

                $stmt->close();
            }
        }
        $i++;
    }

Answer 6

If you are trying to insert huge bulk data in parts by using limit, you are operating within the initial constraints laid down by the MySQL.

Try increasing the values of the constraints rather : PFB

Variables : max_allowed_packet, bulk_insert_buffer_size, key_buffer_size

Sample queries to show and set :

show variables like 'max_allowed_packet';
SET GLOBAL max_allowed_packet=524288000;

References:
http://forums.mysql.com/read.php?20,161869

MySQL - how many rows can I insert in one single INSERT statement?