Programming the Perl DBI

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 megaliths

This 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 ...