Table Querying   «Prev  Next»
Lesson SQL INSERT statement
Objective Learn how to use the INSERT statement.

General Format for SQL INSERT Statement

The general format of the INSERT statement is:
INSERT INTO table_name (column list) values(value list)

The keyword INTO is optional, but it can help in the readability of your INSERT statement when you review it later.
It will not hurt anything to have it in the statement, but the statement has no meaning to the SQL database engine.
As you might have guessed, table_name refers to the table you want to insert the information into.
In our ongoing example from previous lessons, the table is named Customer. The column list is where you indicate which columns you are providing information for. Keep in mind that columns that you do not provide information for will default to blank, or null values. If your database does not allow nulls for a given column that you have left blank, you will get an error message indicating that you are not allowed to insert blanks for that column value.
This is an example of an INSERT statement:

INSERT INTO MyTable (Lastname, Firstname)
  VALUES ("Gauss", "Karl")
INSERT INTO MyTable (Lastname, Firstname) 
  VALUES ("Newton", "Isaac")
INSERT INTO MyTable (Lastname, Firstname)
  VALUES ("Hilbert", "David")
Here is a tip if you plan to use automatically generated values.
We will continue to examine this statement in the next lesson.

The following example inserts values into the SmartPersons table's LastName and FirstName fields. It gets the values from a query that selects FirstName and LastName values from the Persons table where the corresponding record's State value is not Confusion:
INSERT INTO SmartPersons (LastName, FirstName)
SELECT LastName, FirstName FROM Persons
WHERE State <> 'Confusion'

Unlike the previous INSERT statements, this version may insert many records in the table if the query returns a lot of data.