08 - MySQL Insert

After table creation, we need to insert the data in the table. For data insertion MySQL provides a query INSERT INTO TABLE_NAME. For example:

INSERT INTO tablename (field1, field2, filed3,  ...fieldN ) VALUES ( value1, value2, value3, ...valueN );lueN );

There are two ways to write this Insert query.

  • Insert the data for all columns.
  • Insert the data for some particular columns.

For example, if the table structure is as:

ID

Name

Address

Country

1

Aman

FBD

INDIA

2

Rolls

GJI

NEW ZEALAND

When we want to insert data for all columns then, there is no need to define column names just write the values in the same order of the column names.

        INSERT INTO TABLE1 VALUES(3,’John’,’KOP’,’LONDON’) ;

Then the data is inserted in the table as:

ID

Name

Address

Country

1

Aman

FBD

INDIA

2

Rolls

GJI

NEW ZEALAND

3

John

KOP

LONDON

When we want to insert the data in some columns like only name and address then it needs to include the column name in the query as:

         INSERT INTO TABLE1 (NAME, Address) VALUES (‘John’, ’KOP’).

Then the data is inserted as:

ID

Name

Address

Country

1

Aman

FBD

INDIA

2

Rolls

GJI

NEW ZEALAND

 

John

KOP

 

NOTE: All the values inserted in the table are in single quotes if they are string types.

In insert command the concept of AUTO_INCREMENT comes into the picture.

First the question comes- what is AUTO_INCREMENT?

As the name implies it increases the table counter to value one and is inserted automatically on the insertion of every new row into the table. Auto increment is handled by the MySQL server itself and is maintained in the metadata i.einformation_schema.

For example in the above table if we make the Column ID as auto incremented then, we not need to insert it manually and it enters automatically with the counter plus 1. So the following command will result as:

         INSERT INTO TABLE1 (NAME, Address) VALUES (‘John’, ’KOP’) ;

Then the data is inserted as:

ID

Name

Address

Country

1

Aman

FBD

INDIA

2

Rolls

GJI

NEW ZEALAND

3

John

KOP

 

Here ID=3 inserted automatically on insertion of a new row in the table.

Special use of insert command

We may use the insert command to select the data from a table and insert it into another table, the insert command in that scenario is as follows:

          INSERT INTO NEW_TABLE SELECT * FROM OLD_TABLE;

Then all the rows of OLD_TABLE is copied into the NEW_TABLE, we can also set limit or any condition into the select table with WHERE clause. If we need to copy the few columns of OLD_TABLE into NEW_TABLE then we need to just define the column name in the above command as:

          INSERT INTO NEW_TABLE (filed1, field2) SELECT field1, field2 FROM OLD_TABLE;

Here the column count and its type need to be same although the name may be different. In case of different column name it gives an error and in case of type mismatch it might truncate the data.

Like us on Facebook