Zool! Arty Workshop

Copying records from one table to another: INSERT from SELECT in SQL

Tags: computers, databases, SQL

If you find yourself writing something like the following code, try to stop and think for a while (the example uses PHP Database Objects extension):

$insert = $dbh->prepare('INSERT INTO cheapBooks VALUES (?)');

foreach($dbh->query('SELECT ID FROM Books WHERE price < 10') as $book){
        // Insert cheap books into a temporary table
        $insert->execute(array($book['ID']));
}

Unfortunately, using loops is the first thing that comes to head when you need to copy a table to another table or to insert selected rows from one table to another one.

SQL standard allows copying records from one table to another using just one query. Simply use a select query instead of specifying values for insertion explicitly. Thus, we can rewrite the aforementioned exmaple in the following way:

INSERT INTO cheapBooks (ID) SELECT ID FROM Books WHERE price < 10

Now, let's imagine that the three last books in your stock are secondhand ones. That's why you not only want to mention them in the cheap books table, but also to write a comment why they are so inexpensive:

INSERT INTO cheapBooks (ID, NOTE)
SELECT ID, 'Was in use'
       FROM Books WHERE ID > (SELECT MAX(ID) FROM Books) - 3

And of course, if you need to copy all records from one table to another, it's as simple as follows:

INSERT INTO Books_copy SELECT * FROM Books

Please notice that if you really need to duplicate a table and not to copy all records from one table to an another one, then maybe your database vendor has a nice solution for this particular task. For example, MySQL has CREATE TABLE ... SELECT and PgSQL 9.2 has CREATE TABLE AS.

Also consult your RDBMS documentation for details about inserting from a select:

• INSERT ... SELECT syntax в MySQL: http://dev.mysql.com/doc/refman/5.1/en/insert-select.html
• INSERT in PostreSQL: http://www.postgresql.org/docs/9.0/interactive/sql-insert.html

24 August 2011