Using INSERT for data transfers
One
of the more sneaky uses for the
INSERT keyword is to transfer data from one table
or column to another in one easy operation. This seems to fly in the
face of our previous assertion that only one row can be inserted with
each INSERT statement, but in fact, follows the
rules correctly (in an underhand manner).
For example, if we wanted to make a quick copy of the
megaliths table into a new table called
megaliths_copy, the following SQL statement can be
used:
INSERT INTO megaliths_copy
SELECT *
FROM megalithsThis process inserts each row returned from the
SELECT statement into the new table, row by row,
until an exact copy is created. This feature of
INSERT is extremely useful for making quick copies
of tables if you need to do some destructive maintenance work on the
original, such as pruning redundant data. For this SQL to work, the
original table and destination table must have an identical
structure.
You can further refine this operation by specifying conditions that the rows to be transferred must meet before being inserted. For example, to copy across only the rows of data for megaliths located in Wiltshire:
INSERT INTO megaliths_copy
SELECT *
FROM megaliths
WHERE location LIKE '%Wiltshire%'Furthermore, you can make extracts of data from tables into new tables by explicitly specifying the targeted columns in the new table. This is useful when building large denormalized tables for use within a data warehouse. Therefore, if we had a table called ...