mysql  insert into statement

the MySQL insert into statement is used to insert new records in a database.

in other word the mysql insert statement allows you to insert data into tables.

mysql insert into syntax

you can write the insert into statement in two forms.

the first form does not specify the column names where the data will be inserted,only their values:

             insert into      table_name

             values (value1, value2, value3 ....);

the second form specifies both column names and the values to be inserted.

            insert into      table_name   (column1,column2,....)

             values (value1, value2, value3 ....);

we can insert more than one record at a time using insert into statement.

            insert into      table_name   (column1,column2,....)

             values            (value1, value2, value3 ....),
              
                                   (value1, value2, value3 ....),
                                                 
                                   (value1, value2, value3 ....),
                                                 
                                   ....;

note :- the into keyword in an insert statement is optional.


demo database table

below is a selection from the "user" table:

useridfirstnamelastnamemobilenocity
1sandeepkumar9549293245india
2ramkumar9549293212usa

MySQL insert into example

to insert record use the following mysql statement without specifying column names:

              insert into         user

              values  ('shyam', 'sing',  9549293345, 'uae');

or to insert record use the following mysql statement including column names:

               insert into         user (firstname, lastname, mobileno, city)

               values  ('krishna', 'nehra',  9649293345, 'uk');

output :-

the selection from the "user" table will now look like this:

useridfirstnamelastnamemobilenocity
1sandeepkumar9549293245india
2ramkumar9549293212usa
3shyamsing9549293345use
4krishnanehra9649293345uk

MySQL Inset Into Example - insert multiple rows

Multiple records can be entered in a single INSERT statement through the use of multiple VALUES() clauses within the same statement.

               insert into         user (firstname, lastname, mobileno, city)

               values  ('krishna', 'nehra',  9649293345, 'uk'),
                                         
                           ('ankit', 'nehra', 0121212122, 'canada'),
                                                    
                           ('vinod', 'puniya', 8834343434, 'india');

output : try yourself

note : you don't have to specify the value for auto-increment column.because mysql generates its value automatically.


MySQL insert Into Statement with select clause

you can specify the values for the insert statement from a select statement.it is very handy because you can clone a table fully or partially by using the insert and select clause.

syntax:

            insert into     table_name

            select  column_name1, column_name2,...   
                         
            from  table_name2;
               insert into       user

               select   *
                      
               from     info;

output :- try yourself

When creating sequences with AUTOINCREMENT columns, omitting the field name in the INSERT statement will cause MySQL to automatically generate the next number in the sequence.This number serves well as a primary key for your table.