» Basic MySQL Tutorial » Inserting Data into Database Tables

Inserting Data into Database Tables

Summary: In the previous tutorials you learn different ways to query data from database table by using SQL SELECT statement. Are you wonder that how data is added into those table? In this tutorial, you will learn do it 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 forms are listed as follows:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] table_name [(column_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...

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

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

 

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 and insert 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.

Search