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.
Tweet
add to del.icio.usRelated 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
Trackbacks
Trackback Address: 452/1C3CE4A4E90161986A01075FD75FB49B
Comments
On 08/30/2005 at 7:23:23 PM EDT Tim Fanelli wrote:
1
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.
On 07/20/2007 at 4:37:44 AM EDT SHAIIN wrote:
2
I WANT TO INSERT 100 RECORDS AT A TIME ..HOW CAN I BUILD THIS QUERY??
Post a Comment
Recent Entries
- Nginx redirect www to non www domain
- HashDOS and ColdFusion
- HackMyCF Updated for APSB11-29 Security Hotfix
- Adobe eSeminar on FuseGuard
- Determining Which Cumulative Hotfixes are Installed on ColdFusion
- Adding Two Factor Authentication to ColdFusion Administrator
- ColdFusion Developer Week at Adobe.com
- Bug Loading Scripts for CFFileUpload and CFMediaPlayer
