MySQL Tutorials for Business Analyst: MySQL INSERT INTO Query: How to add Row in Table


INSERT INTO is used to store data in the tables. The INSERT command creates a new row in the table to store data. The data is usually supplied by application programs that run on top of the database.

Basic syntax

Let’s look at the basic syntax of the INSERT INTO MySQL command:

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


  • INSERT INTO `table_name` is the command that tells MySQL server to add a new row into a table named `table_name.`
  • (column_1,column_2,…) specifies the columns to be updated in the new MySQL 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:

  • 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’.



Suppose that we have the following list of new library members that need to be added to 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

Let’s 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.

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 in MySQL 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 omitted 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 MySQL insert statement. If we are supplying values for ALL the columns in the table, then we can omit the columns from the MySQL insert query.


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

Let’s now use the SELECT statement to view all the rows in the member’s table.

SELECT * FROM `members`;
membership_ number full_ names gender date_of_ birth physical_address postal_ address contct_ number email
1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542
2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL
3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345
4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL
5 Leonard Hofstadter Male NULL Woodcrest NULL 845738767 NULL
6 Sheldon Cooper Male NULL Woodcrest NULL 976736763 NULL
7 Rajesh Koothrappali Male NULL Woodcrest NULL 938867763 NULL
8 Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL
9 Howard Wolowitz Male 24-08-1981 SouthPark P.O. Box 4563 987786553

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_id category_name remarks
1 Comedy Movies with humour
2 Romantic Love stories
3 Epic Story acient movies
4 Horror NULL
5 Science Fiction NULL
6 Thriller NULL
7 Action NULL
8 Romantic Comedy NULL
9 Cartoons NULL
10 Cartoons NULL


  • The INSERT command is used to add new data into a table. MySql will add a new row, once the command is executed.
  • 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.


Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!


Beginners tutorial with R – Strings

MySQL Tutorials for Business Analyst: How to use Views in MySQL

Beginners Guide to Python 3 – Python Variables Scope