Java Reference
In-Depth Information
Using INSERT ... SELECT
The
INSERT
statement illustrated in the example of
Listing 6-1
is primarily intended for inserting
records into a table one at a time. For applications such as storing information from membership
applications or entering employee records, this is the perfect solution. However, there are times when
you want to copy subsets of data from one table to another. On these occasions, doing the transfer
one record at a time introduces a lot of overhead because each record has to be individually retrieved
from one table and inserted into another other.
SQL allows you to handle these situations by combining the
INSERT
command with a
SELECT
command, which queries the database for the desired records. The advantage of this approach is that
the whole process is carried out within the RDBMS, avoiding the overhead of retrieving records and
reinserting them externally.
The SELECT statement
The
SELECT
statement is used to query the database for specific rows. This is the basic form of the
SELECT
statement:
SELECT
Field1, Field2, ...
FROM
TableName
[ WHERE ... ];
In place of a comma-delimited list of field names, you can supply the asterisk wildcard character, *, to
request all fields:
SELECT * FROM TableName;
Cross-Reference
The
SELECT
statement is discussed in detail in
Chapter 7
.
An example of a situation where you might use
INSERT...SELECT
is the creation of a table
containing only the first and last names from the Contact_Info Table. As illustrated in
Chapter 5
,
the
SQL command to create the table is as follows:
CREATE TABLE Names
(First_Name VARCHAR(20), Last_Name LName VARCHAR(30));
To insert the corresponding data from the original Contact_Info Table, use a SQL
INSERT...SELECT
command to select the desired fields from the Contact_Info Table, and insert them into the new Names
Table. Here's an example:
INSERT INTO Names
SELECT First_Name, Last_Name FROM Contact_Info;
Essentially, this command tells the database management system to perform these two separate
operations internally:
