Java Reference
In-Depth Information
INSERT INTO tableName (colName1, colName2, ...) VALUES (value1, value2, ...);
To insert name and address information into the Contact_Info Table we create in
Chapter 5
, use a
SQL
INSERT
statement like this:
INSERT INTO Contact_Info
(FName, MI, LName, Email)
VALUES
('Michael','X','Corleone','offers@cosa_nostra.com');
Notice how the field names have been specified in the order in which you plan to insert the data. This
insert will work just as well if you use the following command:
INSERT INTO Contact_Info
(Email, LName, FName, MI)
VALUES
('offers@cosa_nostra.com','Corleone','Michael','X');
You can also use a shorthand form if you know the column order of the table. Here's an example:
INSERT INTO Contact_Info
VALUES
('Michael','X','Corleone','offers@cosa_nostra.com');
Note
String data is specified in single quotes ('), as shown in the examples. Numeric values
are specified without quotes.
Follow these rules when inserting data into a table with the
INSERT
statement:
The column names you use must match the names defined for the column.
The values you insert must match the data type defined for the column they are being inserted
into. You can't, for example, put string data into a numeric field.
The data size must not exceed the column width, so you can't put 30 character names into 20
character fields.
The data you insert into a column must comply with the column's data constraints; for example,
you can't put the last names of all members of the Corleone family into a column if you have
constrained that column as
UNIQUE.
These rules are obvious, but breaking them accounts for a lot of SQL exceptions, particularly when you
save data in the wrong field order. Another common error is to try and insert the wrong number of data
fields.
When the Contact_Info Table is defined, the MI field is defined as NULLABLE. The correct way to
insert a NULL is this:
INSERT INTO Contact_Info
(FName, MI, LName, Email)
