Skip to Main Content
Programming the Perl DBI
book

Programming the Perl DBI

by Tim Bunce, Alligator Descartes
February 2000
Intermediate to advanced content levelIntermediate to advanced
364 pages
11h 47m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Advanced Perl Programming, 2nd Edition

Advanced Perl Programming, 2nd Edition

Simon Cozens
Perl One-Liners

Perl One-Liners

Peteris Krumins
Advanced Perl Programming

Advanced Perl Programming

Sriram Srinivasan
Beginning Perl

Beginning Perl

Curtis Ovid Poe

Publisher Resources

ISBN: 1565926994Supplemental ContentErrata Page