In the previous sections, we
have seen how to retrieve information
from tables. But how do these
rows of data get into these
tables in the first place? This
is what this section, covering
the INSERT statement,
and next section, covering tbe UPDATE statement,
are about.
In SQL, there are essentially
basically two ways to INSERT data
into a table: One is to insert
it one row at a time, the other
is to insert multiple rows at
a time. Let's first look at
how we may INSERT data
one row at a time:
The syntax for inserting data
into a table one row at a time is
as follows:
INSERT
INTO "table_name" ("column1",
"column2", ...)
VALUES ("value1", "value2", ...)
Assuming that we have a table
that has the following structure,
Table Store_Information
| Column Name |
Data Type |
| store_name |
char(50) |
| Sales |
float |
| Date |
datetime |
and now we wish to insert one
additional row into the table
representing the sales data for
Los Angeles on January 10, 1999.
On that day, this store had $900
in sales. We will hence use the
following SQL script:
INSERT
INTO Store_Information (store_name,
Sales, Date)
VALUES ('Los Angeles', 900,
'Jan-10-1999')
The second type of INSERT
INTO allows
us to insert multiple rows into
a table. Unlike the previous
example, where we insert a single
row by specifying its values
for all columns, we now use
a SELECT statement
to specify the data that we
want to insert into the table.
If you are thinking whether
this means that you are using
information from another table,
you are correct. The syntax
is as follows:
INSERT INTO
"table1" ("column1", "column2",
...)
SELECT "column3", "column4", ...
FROM "table2"
Note that this is the simplest
form. The entire statement can
easily contain WHERE, GROUP
BY, and HAVING clauses,
as well as table joins and aliases.
So for example, if we wish to
have a table, Store_Information,
that collects the sales
information for year 1998, and you
already know that the source data
resides in the Sales_Information
table, we'll type in:
INSERT
INTO Store_Information (store_name,
Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
