RelationalDBDesignRelationalDBDesign


Table Querying   «Prev  Next»
Lesson 6 The INSERTstatement
Objective Create an initial INSERT statement that will place one row's worth of information into the table.

INSERT one Row into Database Table

For reference, here is the generic form of the INSERT statement again:
INSERT INTO table_name (column list) values(value list)

The final item, the values that will be inserted, is represent the core of the INSERT statement. This is where you provide the data to be inserted into the table.

Enclose text items

Be sure you enclose text items in single or double quotation marks (depending on the database). Otherwise, the database engine might complain about the syntax of your statement. Experiment if you are not sure whether you need single or double quotes.
So, the table has the following types (from the CREATE TABLE statement in the last lesson):

CustomerID    numeric(18, 0) NOT NULL
LastName      char (50)      NULL
FirstName     char (50)      NULL
Address1      char (100)     NULL
Address2      char (100)     NULL
City          char (25)      NULL
State         char (2)       NULL
ZipCode       char (10)      NULL
Phone         char (20)      NULL

You will need to list these columns and values in your INSERT statement.

Insert Statement

Now that all the pieces are in place, it's time to add some data. The following statement creates a row in the person table for William Turner:
mysql> INSERT INTO person
-> (person_id, fname, lname, gender, birth_date)
-> VALUES (null, 'William','Turner', 'M', '1972-05-27');
Query OK, 1 row affected (0.01 sec)

The feedback ("Query OK, 1 row affected") tells you that your statement syntax was proper, and that one row was added to the database (since it was an insert statement). You can look at the data just added to the table by issuing a select statement:
mysql> SELECT person_id, fname, lname, birth_date
-> FROM person;
+-----------+---------+--------+------------+
| person_id | fname | lname | birth_date |
+-----------+---------+--------+------------+
| 1 | William | Turner | 1972-05-27 |
+-----------+---------+--------+------------+
1 row in set (0.06 sec)

Insert Statement - Exercise

Click the Exercise link below to add a row of information to Customer Table.
Insert Statement - Exercise