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

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.

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.