What's faster - loop for insert or insert...select.

[mr. modus]

What is faster if I'm moving large numbers of records (anywhere from 10,000 to
300,000 records per archive) from one query to another table?

1) Query master table, loop through result set and insert into archive table 1
record at a time.
or
2) Have this:
Insert into archive
select * from mastertable
where (condition=value)

[Dan Bracuk]

Option 2 is better by far.

Option 2 without using cold fusion is even better if that option is available to you.

[TheCoolLion]

If you use a database that handles stored procedures do it in the database.
The database server always gives you the best performance.