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 describes this technique, where query is any valid SQL query which returns values which will be suitable for insertion into the table called table_name :
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 to expect them. You do not provide the VALUES keyword with this form of INSERT INTO , however, instead providing a complete SQL SELECT statement in its place.
For example, imagine that Book Town keeps a table called book_queue , which holds books waiting to be approved. When approved, those values need to be moved from the queue, and into the normal books table. This can be achieved with the syntax demonstrated in Example 4-17 .
Example 4-17. 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. This is performed by way of a SELECT statement which 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 had a single row been inserted. The second number, as with a normal INSERT INTO , returns the number of rows inserted (in this case, 2).