The SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.
The first form does not specify the column names where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1,value2,value3,...);
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCod Countr
e y
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resend 08737-363 Brazil
e
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
Assume we wish to insert a new row in the "Customers" table.
We can use the following SQL statement:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
Try it yourself »
The selection from the "Customers" table will now look like this:
CustomerID CustomerName ContactName Address City PostalCod Countr
e y
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
Importadora
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite Seattle 98128 USA
3B
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal Tom B. Skagen 21 Stavanger 4006 Norway
Erichsen
Did you notice that we did not insert any number into the CustomerID field?
The CustomerID column is automatically updated with a unique number for each record in the table.
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.
The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field
will of course also be updated automatically):
Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
Try it yourself »
The selection from the "Customers" table will now look like this:
CustomerID CustomerName ContactName Address City PostalCod Countr
e y
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
Importadora
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite Seattle 98128 USA
3B
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
Edit the SQL Statement, and click "Run SQL" to see the result.
INSERT INTO Customers (CustomerName, Ci
VALUES ('Cardinal', 'Stavanger', 'Norw ay');
Result:
You have made changes to the database. Rows affected: 1