SQL INSERT INTO

The SQL INSERT INTO clause is used to insert data into a SQL table. The SQL INSERT INTO is frequently used and has the following generic syntax:

INSERT INTO Table1 (Column1, Column2, Column3) VALUES (ColumnValue1, ColumnValue2, ColumnValue3)

The SQL INSERT INTO clause has actually two parts - the first specifying the table we are inserting into and giving the list of columns we are inserting values for, and the second specifying the values inserted in the column list from the first part.

Consider the Users table from the previous chapter:

FirstNameLastNameDateOfBirthEmailCity
JohnSmith12/12/1969john.smith@john-here.comNew York
DavidStonewall01/03/1954david@sql-tutorial.comSan Francisco
SusanGrant03/03/1970susan.grant@sql-tutorial.comLos Angeles
PaulO'Neil09/17/1982paul.oneil@pauls-email.comNew York
StephenGrant03/03/1974sgrant@sgrantemail.comLos Angeles

If we want to enter a new data row into the Users table, we can do it with the following SQL INSERT INTO statement:

INSERT INTO Users (FirstName, LastName, DateOfBirth, Email, City) VALUES ('Frank', 'Drummer', '10/08/1955', 'frank.drummer@frankdrummermail.com', 'Seattle')

If we select all the data from the Users table after we have executed the SQL INSERT INTO above, we'll get the following result:

FirstNameLastNameDateOfBirthEmailCity
JohnSmith12/12/1969john.smith@john-here.comNew York
DavidStonewall01/03/1954david@sql-tutorial.comSan Francisco
SusanGrant03/03/1970susan.grant@sql-tutorial.comLos Angeles
PaulO'Neil09/17/1982paul.oneil@pauls-email.comNew York
StephenGrant03/03/1974sgrant@sgrantemail.comLos Angeles
FrankDrummer10/08/1955 frank.drummer@frankdrummermail.comSeattle

One interesting question about SQL INSERT INTO is, would it be possible to insert values for only part of the columns, instead for all of them? The answer is yes, as long as the columns that we are skipping can have NULL value or have default value specified. Here is an example of using SQL INSERT INTO to insert a new row and supply values for only the first 4 columns of the Users table:

INSERT INTO Users (FirstName, LastName, DateOfBirth, Email) VALUES ('Frank', 'Drummer', '10/08/1955', 'frank.drummer@frankdrummermail.com')

In the above example we assumed that the values in the last column Email can be NULL values. The result of the SQL INSERT above will be:

FirstNameLastNameDateOfBirthEmailCity
JohnSmith12/12/1969john.smith@john-here.comNew York
DavidStonewall01/03/1954david@sql-tutorial.comSan Francisco
SusanGrant03/03/1970susan.grant@sql-tutorial.comLos Angeles
PaulO'Neil09/17/1982paul.oneil@pauls-email.comNew York
StephenGrant03/03/1974sgrant@sgrantemail.comLos Angeles
FrankDrummer10/08/1955 frank.drummer@frankdrummermail.comNULL

If you are inserting a new row and you are supplying values for all columns, then you can skip the entire column list in your statement. For example the following two SQL INSERT statements are equivalent:

INSERT INTO Users VALUES ('Frank', 'Drummer', '10/08/1955', 'frank.drummer@frankdrummermail.com', 'Seattle')


INSERT INTO Users (FirstName, LastName, DateOfBirth, Email, City) VALUES ('Frank', 'Drummer', '10/08/1955', 'frank.drummer@frankdrummermail.com', 'Seattle')

However if you skip the column list and do not supply value for all columns at the same time, then you'll get an error as a result of your SQL INSERT INTO execution:

INSERT INTO Users VALUES ('Frank', 'Drummer', '10/08/1955', 'frank.drummer@frankdrummermail.com')

The above SQL INSERT will produce error, because we haven't specified value for the City column.