Database Reference
In-Depth Information
• For a data-loading operation based on information that might be malformed, load
new rows into a temporary table, perform some preliminary checks, and correct
the rows as necessary. When you're satisfied that the new rows are okay, copy them
from the temporary table to your main table.
• Some applications maintain a large repository table and a smaller working table
into which rows are inserted on a regular basis, copying the working table rows to
the repository periodically and clearing the working table.
• To perform summary operations on a large table more efficiently, avoid running
expensive summary operations repeatedly on it. Instead, select summary informa‐
tion once into a second table and use that for further analysis.
This section shows how to retrieve a result set into a table. The table names
src_tbl
and
dst_tbl
in the examples refer to the source table from which rows are selected and
the destination table into which they are stored, respectively.
If the destination table already exists, use
INSERT
…
SELECT
to copy the result set into
it. For example, if
dst_tbl
contains an integer column
i
and a string column
s
, the
following statement copies rows from
src_tbl
into
dst_tbl
, assigning column
val
to
i
and column
name
to
s
:
INSERT
INTO
dst_tbl
(
i
,
s
)
SELECT
val
,
name
FROM
src_tbl
;
The number of columns to be inserted must match the number of selected columns,
with the correspondence between columns based on position rather than name. To copy
all columns, you can shorten the statement to this form:
INSERT
INTO
dst_tbl
SELECT
*
FROM
src_tbl
;
To copy only certain rows, add a
WHERE
clause that selects those rows:
INSERT
INTO
dst_tbl
SELECT
*
FROM
src_tbl
WHERE
val
>
100
AND
name
LIKE
'A%'
;
The
SELECT
statement can produce values from expressions, too. For example, the fol‐
lowing statement counts the number of times each name occurs in
src_tbl
and stores
both the counts and the names in
dst_tbl
:
INSERT
INTO
dst_tbl
(
i
,
s
)
SELECT
COUNT
(
*
),
name
FROM
src_tbl
GROUP
BY
name
;
If the destination table does not exist, create it first with a
CREATE
TABLE
statement, then
copy rows into it with
INSERT
…
SELECT
. Alternatively, use
CREATE
TABLE
…
SELECT
to
create the destination table directly from the result of the
SELECT
. For example, to create
dst_tbl
and copy the entire contents of
src_tbl
into it, do this:
CREATE
TABLE
dst_tbl
SELECT
*
FROM
src_tbl
;
