Multiple Dynamic Inserts with SQL

August 30, 2005

If you have ever had to do lots of SQL INSERT statements you will know that they can get pretty slow. When looking for ways to speed up some inserts, I noticed that you can use a SELECT subquery to provide the values. Like this:

INSERT INTO foo_archive (id, title)
  SELECT id, title
  FROM foo

This should work on Microsoft SQL Server, MySQL, PostgreSQL, and Oracle.

Permalink | Add Comment |

add to del.icio.us | Tags: sql, insert, select, subquery

Related Entries

SQL to Select a random row from a database table - September 14, 2005
Faster Inserts with PostgreSQL - August 31, 2005
Insert Delayed with MySQL - August 2, 2005
Multiple Inserts with MySQL - June 10, 2005

7 people found this page useful, what do you think?

Comments

I've done these types of inserts on IBM's DB2 also. It's a great trick when normalizing poorly designed tables, or to force a reorder when adding indices to a table.

by Tim Fanelli on 08/30/2005 at 5:23:23 PM UTC