MySQL INSERT INTO

MySQL INSERT INTO Statement

To add new records to a table, use the INSERT INTO statement.

INSERT INTO Syntax

Two potential formats for the INSERT INTO statement are as follows:

1. Indicate the columns to insert the values into as well as their names:

				
					INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. You do not need to provide the column names in the SQL query if you are adding data to every column in the table. Make sure, though, that the values are arranged in the same order as the table’s columns. In this case, the INSERT INTO syntax would look like this:

				
					INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 – 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland

INSERT INTO Example

A new record is inserted into the “Customers” database using the SQL statement that follows:

Example

				
					INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

The selection from the “Customers” table will now look like this:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 – 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	Tom B. Erichsen	Skagen 21	Stavanger	4006	Norway

Have you noticed that the CustomerID column has no numbers in it?
Whenever a new record is added to the table, the auto-increment CustomerID column is created automatically.

Insert Data Only in Specified Columns

You may also choose which columns to insert data into.

The new record that is created by the SQL statement that follows will only have data entered into the “CustomerName,” “City,” and “Country” columns (the CustomerID will be updated automatically).

Example

				
					INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

The selection from the “Customers” table will now look like this:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 – 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	null	null	Stavanger	null	Norway

MySQL INSERT INTO