Hi All !

I would like to know what would be the performance trade off in using a
INSERT INTO....SELECT ....(mass insert)
versus
Loop the SELECT and INSERT statements (one insert at a time)

By looping a select stmt followed by a insert, I can see the benefit of issuing intermittent commits.
In terms of performance, how does the masss insert compare with a SELECT-INSERT loop ? Any article to read more on this topic.


Anil

Performance wise the mass insert will always be better,

only when having problems with logging and locking space (as you said already, commit counts), I would loop.

if you have a lock/log space problem, another option would be to load from a cursor.

You can split it up and still use your INSERT... SELECT.

See this thread:
http://www.dbforums.com/t1201769.html