Inserting Data into Database Tables

In this tutorial, you will learn how to insert data into database tables by using SQL INSERT statement.

INSERT Statement

INSERT statement allows you to insert one or more rows to the table. In MySQL, the INSERT statement form is listed as follows:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] table_name [(column_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE column_name=expression, ... ]

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] table_name [(column_name,...)]
        SELECT statement

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]         
 [INTO] table_name         
 SET column_name=(expression | DEFAULT), ...         
 [ ON DUPLICATE KEY UPDATE column_name=expression, ... ]

As you can see INTO in the INSERT statement is optional. In the first form, you insert a new data row into an existing table by specifying the column name and data for each. As an example to insert a new office to the offices table in the sample database you can do as follows:

INSERT INTO classicmodels.offices 
 (officeCode, 
 city, 
 phone, 
 addressLine1, 
 addressLine2, 
 state, 
 country, 
 postalCode, 
 territory
 )
 VALUES
 ('8', 
 'Boston', 
 '+1 215 837 0825', 
 '1550 dummy street', 
 'dummy address', 
 'MA', 
 'USA', 
 '02107', 
 'NA'
 )

In the second form, instead of providing explicit data, you select it from other table by using SELECT statement. This form allows you to copy some or some part of data from other table to the inserted table. As an example, we can create a temporary table andinsert all offices which locate in US into that one by using this query:

INSERT INTO temp_table 
 SELECT * FROM offices WHERE country = 'US'  

The third form enables you to specify the column you want to insert the data. For example, we have the query like this:

INSERT INTO productlines  
 SET productLine = 'Luxury Cars' 

It means we only insert the data into productLine column in productLines table.