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`

Backplate

Answer 1

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?

As pst noted in a now deleted comment, the LIMIT is actually part of the SELECT, and has nothing to do with the INSERT itself.

John

You'd think it'd be obvious, right? If you want to limit the amount of data you're inserting, don't supply as many values. LIMIT in an INSERT INTO ... SELECT FROM call is relevant as demonstrated here. – Rodolforodolph

LIMIT can be used with UPDATE and DELETE too. So it's not part of SELECT only. – Tetracycline

Answer 2

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.

Wald

Answer 3

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?

Offen

Answer 4

LIMIT 2 will only work with select no values

Contrayerva

Answer 5

If you want to partially copy an existing table, you could do the following (I needed this to run some tests on that table).

CREATE TABLE copy SELECT * FROM source_table LIMIT 1000;

You can of cause use a temporary table to copy also from one table into another.

CREATE TEMPORARY TABLE copy SELECT * FROM source_table LIMIT 1000;
INSERT INTO target_table SELECT * FROM copy;
DROP TABLE copy;

Topdress

Answer 6

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++;
    }

Mendicity

Answer 7

no you cannot use limit in insert query

Eaglet