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:
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:
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:
|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:
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:
And after selecting all entries in the table you will have this:
|BMW||3 Series Coupe||2009||NULL|