INSERT INTO sql

INSERT INTO sql

 

What is INSERT INTO?

The main purpose of database systems is to store data in tables. The data is usually provided by application programs that run on top of the database. For this purpose, SQL has an INSERT command that is used to store data in a table. The  INSERT command creates a new row  in a table to store data.

Basic Syntax

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

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

HERE

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

When providing data values ​​to insert into a new table, keep the following in mind when working with different types of data.

  • String data types  - All string values ​​must be enclosed in single quotes.
  • Numeric data types  - All numeric values ​​should be specified 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 we have the following list of new library members that need to be added to the database.

Full names Date of Birth Floor Physical adress mailing address contact number E-mail address
Leonard Hofstadter   the male Woodcrest   0845738767  
Sheldon Cooper   the male Woodcrest   0976736763  
Rajesh Koothrappali   the male fairview   0938867763  
Leslie Winkle 02/14/1984 the male     0987636553  
Howard Wolowitz 08/24/1981 the male South Park PO Box 4563 0987786553 lwolowitz@email.me

 

Let's insert the data one by one. Let's start with Leonhard Hofstadter. We will treat the contact number as a numeric data type rather than enclosing it in single quotes.

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

 

Running the above script resets 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 will be removed as it has no value.

To avoid such issues, 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 reset

Changing the column order does not affect the INSERT query if the correct values ​​were 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 omitted a date of birth column,  by default MySQL will insert NULL values ​​into columns that are omitted in an INSERT query.

 Let's now insert an entry for Leslie that has a date of birth. The date value must be enclosed in single quotes in 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 columns and mapped them to the values ​​in the insert statement. If we provide values ​​for ALL columns in the table, then we can omit the columns in the insert query.

Example:-

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

Now let's use the SELECT statement to see all the rows in the members table. SELECT * FROM `members`;

membership_number full_names gender date_of_birth physical_address postal_address contct_number email
one Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 <a href='mailto:janetjones@yagoo ;.cm'>janetjones@yagoo.cm</a>
2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL <a href='mailto:jj@fstreet.com'>jj@fstre et.com</a>
3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 <a href='mailto:rm@tstreet.com'>rm@tstre et.com</a>
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
eight Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL
9 Howard Wolowitz Male 24-08-1981 south park PO Box 4563 987786553 <a href='mailto:lwolowitz@email. ;me'>lwolowitz@email.me</a>

Note that Leonard Hofstadter's contact number has dropped zero (0) from the contact number. Other contact numbers did not drop to 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 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'll name the new category table category_archive. The script below creates a 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.

Now let's insert all the rows from the category table into the category archive table. The script shown below helps us achieve this.

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

 

When executing the above script, all rows from the category table are inserted into the category archive table. Note that the table structures must be the same for the script to work. A more robust script is one that matches the names of the columns in the insert table with the names in the table containing the data .

The query shown below demonstrates its use.

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

 

Executing a SELECT query

SELECT * FROM `categories_archive`

 

gives the following results shown below.

category_id category_name remarks
one Comedy Movies with humor
2 Romantic love stories
3 epic story ancient movies
4 Horror NULL
5 science fiction NULL
6 Thriller NULL
7 action NULL
eight romantic comedy NULL
9 Cartoons NULL
10 Cartoons NULL

Summary

  • The INSERT command is used to add new data to a table
  • Date and string values ​​must be enclosed in single quotes.
  • Numeric values ​​must not be enclosed in quotation marks.
  • The INSERT command can also be used to insert data from one table into another.