SQL INSERT

Now that we know how to read data using SQL SELECT, it's time to learn how to add new data to our table using SQL INSERT INTO. We'll use the table Cars from our previous example to show how to insert a new row in a table.

Here is how the table looks before our INSERT:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Silver
Honda	Accord EX	2012	Grey
Lexus	ES 350	2008	Silver

Now we want to add another car, this time a red BMW 3 Coupe, produced in 2009. To do that we need the following INSERT INTO statement:

INSERT INTO Cars (CarMake, Model, Year, Color) VALUES ('BMW', '3 Series Coupe', 2009, 'Red');

First we start with the INSERT INTO keywords followed by the name of the table we are inserting data into. After that we have a list of columns in brackets, which specify which columns we are inserting values for. On the second line we use the keyword VALUES followed by the actual values for each of the columns listed on the first row. Looking at the values you may notice that the CarMake, Model and Color values are in single quotes, while the Year value is not. A character sequence in single quotes is treated as a string value, which is the case with the 3 columns, while the Year column is an integer number and doesn't need quotes around it.

Important thing to remember about the INSERT INTO statement is that the order of the column names after the table name, has to be the same as the order of values following the VALUES keyword.

If we select all the data from the table after the insert above has been completed, we'll get the following result:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Silver
Honda	Accord EX	2002	Black
Lexus	ES 350	2008	Silver
BMW	3 Series Coupe	2009	Red

The usage of the list of columns in our SQL INSERT INTO statement is optional, if we supply values for all columns in our table. So we'll get the result above with this SQL statement as well:

INSERT INTO Cars VALUES ('BMW', '3 Series Coupe', 2009, 'Red');

You are not obligated to insert values in all columns either, as long as the columns you are not supplying values from can be NULL (allow NULL as value). For example if we assume that the Color column allows NULL values, and you don't know the color of the BMW, yet you still want to insert a record, then the following SQL statement will do the trick:

INSERT INTO Cars (CarMake, Model, Year) VALUES ('BMW', '3 Series Coupe', 2009);

And after selecting all entries in the table you will have this:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Silver
Honda	Accord EX	2002	Black
Lexus	ES 350	2008	Silver
BMW	3 Series Coupe	2009	NULL