MySQL query INSERT INTO Table with Examples

 

What is INSERT INTO?

The main goal of database systems is to store data in the tables. The data is usually supplied by application programs that run on top of the database. Towards that end, SQL has the INSERT command that is used to store data into a table. The INSERT command creates a new row in the table to store data.

Basic syntax

Let's look at the basic syntax of the SQL INSERT command shown below.

INSERT INTO `table_name`(column_1,column_2,...) VALUES (value_1,value_2,...);

HERE

  • INSERT INTO `table_name` is the command that tells MySQL server to add new row into a table named `table_name`.
  • (column_1,column_2,...) specifies the columns to be updated in the  new row
  • VALUES (value_1,value_2,...) specifies the values to be added into the new row

 

When supplying the data values to be inserted into the new table, the following should be considered while dealing with different data types.

  • String data types - all the string values should be enclosed in single quotes.
  • Numeric data types - all numeric values should be supplied directly without enclosing them in single or double quotes.
  • Date data types - enclose date values in single quotes in the format 'YYYY-MM-DD'.

 

Example:

Suppose that we have the following list of new library members that need to be added into the database.

Full names Date of Birth gender Physical address Postal address Contact number Email Address
Leonard Hofstadter   Male Woodcrest   0845738767  
Sheldon Cooper   Male Woodcrest   0976736763  
Rajesh Koothrappali   Male Fairview   0938867763  
Leslie Winkle 14/02/1984 Male     0987636553  
Howard Wolowitz 24/08/1981 Male South Park P.O. Box 4563 0987786553 lwolowitz@email.me

 

Lets' INSERT data one by one. We will start with Leonard Hofstadter. We will treat the contact number as a numeric data type and not enclose the number in single quotes.

INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ('Leonard Hofstadter','Male','Woodcrest',0845738767);  

 

Executing the above script drops the 0 from Leonard's contact number. This is because the value will be treated as a numeric value and the zero (0) at the beginning is dropped since it's not significant.

In order to avoid such problems, the value must be enclosed in single quotes as shown below -

INSERT INTO `members` (`full_names`,`gender`,`physical_address`,`contact_number`) VALUES ('Sheldon Cooper','Male','Woodcrest', '0976736763'); 

 In the above case , zero(0) will not be dropped

Changing the order of the columns has no effect on the INSERT query as long as the correct values have been mapped to the correct columns.

 The query shown below demonstrates the above point.

INSERT INTO `members` (`contact_number`,`gender`,`full_names`,`physical_address`)VALUES ('0938867763','Male','Rajesh Koothrappali','Woodcrest');   

 

The above queries skipped the date of birth column, by default MySQL will insert NULL values in columns that are skipped in the INSERT query.

 Let's now insert the record for Leslie which has the date of birth supplied. The date value should be enclosed in single quotes using the format 'YYYY-MM-DD'.

INSERT INTO `members` (`full_names`,`date_of_birth`,`gender`,`physical_address`,`contact_number`) VALUES ('Leslie Winkle','1984-02-14','Male','Woodcrest', '0987636553');   

All of the above queries specified the columns and mapped them to values in the insert statement.If we are supplying values for ALL the columns in the table, then we can omit the columns from the insert query.

Example:-

INSERT INTO `members` VALUES (9,'Howard Wolowitz','Male','1981-08-24','SouthPark','P.O. Box 4563', '0987786553', 'lwolowitz[at]email.me');

Let's now use the SELECT statement to view all the rows in the members table.SELECT * FROM `members`;

membership_numberfull_namesgenderdate_of_birthphysical_addresspostal_addresscontct_numberemail
1Janet JonesFemale21-07-1980First Street Plot No 4Private Bag0759 253 542janetjones@yagoo.cm
2Janet Smith JonesFemale23-06-1980Melrose 123NULLNULLjj@fstreet.com
3Robert PhilMale12-07-19893rd Street 34NULL12345rm@tstreet.com
4Gloria WilliamsFemale14-02-19842nd Street 23NULLNULLNULL
5Leonard HofstadterMaleNULLWoodcrestNULL845738767NULL
6Sheldon CooperMaleNULLWoodcrestNULL976736763NULL
7Rajesh KoothrappaliMaleNULLWoodcrestNULL938867763NULL
8Leslie WinkleMale14-02-1984WoodcrestNULL987636553NULL
9Howard WolowitzMale24-08-1981SouthParkP.O. Box 4563987786553lwolowitz@email.me

Notice the contact number for Leonard Hofstadter has dropped the zero (0) from the contact number. The other contact numbers have not dropped the zero (0) at the beginning.

Inserting into  a Table from another Table

The INSERT command can also be used to insert data into a table from another table. The basic syntax is as shown below.

INSERT INTO table_1 SELECT * FROM table_2;    	

Let's now look at a practical example, we will create a dummy table for movie categories for demonstration purposes. We will call the new categories table categories_archive. The script shown below creates the table.

CREATE TABLE `categories_archive` (    	  `category_id` int(11) AUTO_INCREMENT,    	  `category_name` varchar(150) DEFAULT NULL,    	  `remarks` varchar(500) DEFAULT NULL,    	  PRIMARY KEY (`category_id`)    	)   

 

Execute the above script to create the table.

Let's now insert all the rows from the categories table into the categories archive table. The script shown below helps us to achieve that.

INSERT INTO `categories_archive` SELECT * FROM `categories`;  

 

Executing the above script inserts all the rows from the categories table into the categories archive table. Note the table structures will have to be the same for the script to work. A more robust script is one that maps the column names in the insert table to the ones in the table containing the data.

The query shown below demonstrates its usage.

INSERT INTO `categories_archive`(category_id,category_name,remarks)  SELECT category_id,category_name,remarks FROM `categories`;

 

Executing the SELECT query

SELECT * FROM `categories_archive`

 

gives the following results shown below.

category_idcategory_nameremarks
1ComedyMovies with humour
2RomanticLove stories
3EpicStory acient movies
4HorrorNULL
5Science FictionNULL
6ThrillerNULL
7ActionNULL
8Romantic ComedyNULL
9CartoonsNULL
10CartoonsNULL

Summary

  • The INSERT command is used to add new data into a table
  • The date and string values should be enclosed in single quotes.
  • The numeric values do not need to be enclosed in quotes.
  • The INSERT command can also be used to insert data from one table into another.