If you already have values within one table (or across several other tables) that you wish to insert into
a separate table, this can also be achieved with the INSERT INTO command. The
following syntax is used for this technique:
INSERT INTO
table_name
[ (
column_name
[, ...] ) ]
query
Similar to the syntax of INSERT INTO presented in the previous section,
you may optionally specify which columns you wish to insert into, and in what order the
query
returns
their values. However, with this form of INSERT INTO, you provide a complete SQL
SELECT statement in the place of the VALUES keyword.
For example, imagine that Book Town keeps a table called book_queue, which holds
books waiting to be approved for sale. When approved, those values need to be moved from the queue, into the normal
books table. This can be achieved with the syntax demonstrated in
Example 4-18.
Example 4-18. Inserting values from another table
booktown=#
INSERT INTO books (id, title, author_id, subject_id)
booktown-#
SELECT nextval('book_ids'), title, author_id, subject_id
booktown-#
FROM book_queue WHERE approved;
INSERT 0 2
The preceding example demonstrates the insertion of two rows from the table
book_queue into the books table by way of a
SELECT statement that is passed to the INSERT INTO command. Any
valid SELECT statement may be used in this context. In this case, the query selects the result of a
function called nextval() from a sequence called book_ids, followed
by the title, author_id and subject_id
columns from the book_queue table.
Since more than one row is being inserted, the INSERT result indicating success returns
0 in place of the OID that would be returned if a single row had been inserted. The second number, as with
a normal INSERT INTO command, returns the number of rows inserted (in this case, 2).
