INSERT INTO VS SELECT INTO PERFORMANCE DIFFERENCE
1. SELECT INTO hold lock on system table while it is creating table, while for INSERT INTO there are no such lock as table is already there. also these lock are hold until the query completes.
these difference become very evident and affecting performance when query execution times are longer. and multiple users are trying to execute same piece of query as later request will have to wait for prior execution to finish before they can obtain lock on the resources again. there wait time can be avoided by creating table first and then inserting data into table that is by using INSERT INTO over SELECT INTO.
For instantaneous queries(small data) this will not be that much of problem.
2. SELECT INTO is not a logged operation while INSERT INTO is
1. SELECT INTO hold lock on system table while it is creating table, while for INSERT INTO there are no such lock as table is already there. also these lock are hold until the query completes.
these difference become very evident and affecting performance when query execution times are longer. and multiple users are trying to execute same piece of query as later request will have to wait for prior execution to finish before they can obtain lock on the resources again. there wait time can be avoided by creating table first and then inserting data into table that is by using INSERT INTO over SELECT INTO.
For instantaneous queries(small data) this will not be that much of problem.
2. SELECT INTO is not a logged operation while INSERT INTO is
