Table Querying   «Prev  Next»
Lesson 4 Inserting information
Objective Understand what the INSERT statement does.

SQL Insert Statement

Now that you have created your table, it's time to put some information into it. You might wonder why this step is important.
Why not just use an application to put in the information you want to save?
That's a good question, and it's easy to answer.
The reason we want to go through this "manual" step is to show you exactly what applications are doing when you save that information to the database. When the application saves information to the underlying database, it generates the SQL statements and sends them to the database for you.

INSERT INTO

The INSERT INTO statement makes inserting new data into the database very easy. All you need to do is specify into which table you want to insert data, into which columns to insert data, and finally what data to insert.
The basic syntax is as follows:
INSERT INTO table_name (column_names) VALUES (data_values)

This line of code adds a record to the Category table:
INSERT INTO Category (CategoryId, Category) VALUES (1, 'Thriller');

Inserting data is simply a matter of listing each column name (separated by a comma) in the brackets after the table name. In the brackets after the VALUES statement, simply list each item of data to go into the matching column and separate each wih a comma. Character and date data must be wrapped up inside single quotes.
The next lesson is an introduction to the INSERT statement, which is used to add information directly to the database.

SQL Database Programming

SQL INSERT Statement

The INSERT statement adds data to a table in the database. This command has several variations.
For the following examples, assume the Persons table was created with the following command:
CREATE TABLE IF NOT EXISTS Persons (
idPerson INT NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(45) NOT NULL DEFAULT '<missing>',
LastName VARCHAR(45) NOT NULL DEFAULT '<none>',
State VARCHAR(10) NULL,
PRIMARY KEY (idPerson)
)

The simplest form of the INSERT statement lists the values to be inserted in the new record after the VALUES keyword. The values must have the correct data types and must be listed in the same order as the fields in the table.
The following command inserts a new record in the Persons table:
INSERT INTO Persons VALUES (1, "John", "Doe", "MI")

Some databases will not let you specify values for AUTO INCREMENT fields such as idPerson in this example. If you specify the value null for such a field, the database automatically generates a value for you. If you replace a value with the keyword DEFAULT, the database uses that field's default value if it has one. When it executes the following command, the database automatically generates an idPerson value, the FirstName value defaults to < missing>, the LastName value is set to Markup, and the State value is set to null:
INSERT INTO Persons VALUES (null, DEFAULT, "Markup", null)
The next form of INSERT statement explicitly lists the fields that it will initialize. The values in the VALUES clause must match those listed earlier and they must be in the correct order. Listing the fields that you are going to enter lets you omit some fields or change the order in which they are given. The following statement creates a new Persons record. It explicitly sets the FirstName field to Karl and the State field to Confusion. The database automatically generates a new idPerson value and the LastName value gets its default value <none>:
INSERT INTO Persons (FirstName, State) 
VALUES ("Karl", "Confusion")
The final version of INSERT INTO described here gets the values that it will insert from a SELECT statement (described in the next section) that pulls values from a table.