SQL INSERT INTO

The SQL INSERT INTO clause facilitates the process of inserting data into a SQL table. Here is how you can insert a new row into the Weather table, using SQL INSERT INTO:

INSERT INTO Weather (City, AverageTemperature, Date) VALUES ('Los Angeles', 20, '10/10/2005')

The result of the execution of the SQL INSERT INTO above will look like this:

CityAverageTemperatureDate
New York22 C10/10/2005
Seattle21 C10/10/2005
Washington20 C10/10/2005
Los Angeles20 C10/10/2005

You can produce the same result, with a slightly modified SQL INSERT INTO syntax:

INSERT INTO Weather VALUES ('Los Angeles', 20, '10/10/2005')

You are allowed to omit the list of column names in the SQL INSERT INTO clause, if you enter values for each of the table columns.

When using SQL INSERT INTO you might not want to enter values for all columns and in this case you have to specify the list of columns you are entering values for. If you do not enter values for all columns, then the columns you have omitted must allow NULL values or at least have a default value defined. The following SQL INSERT example enters only 2 of the 3 columns in the Weather table:

INSERT INTO Weather (City, Date) VALUES ('Boston', '10/10/2005')

The result of this SQL INSERT will be as follows:

CityAverageTemperatureDate
New York22 C10/10/2005
Seattle21 C10/10/2005
Washington20 C10/10/2005
Los Angeles20 C10/10/2005
BostonNULL10/10/2005

We've inserted a new row for Boston, but we haven't received the temperature value for 10/10/2005 that's why we didn't enter it.