Eric M Russell (12/25/2012)
The reason why SELECT INTO <new table> is faster than INSERT INTO <existing table> is because SELECT INTO doesn't transaction log the data pages being inserted. In the event of rollback, SQL Server simply has to discard the table. However, when inserting into an existing table, SQL Server has to keep track of what pages are being modified to support a rollback.
Just a small correction, there is minimal logging which is done. It is not correct to say that "NO transaction log is created". You can use the below query to verify the transaction log which is created.
SELECT TOP 10 *
FROM Fn_dblog(NULL, NULL)
WHERE allocunitname = <Your_table_name>
ORDER BY [Log Record Length] DESC
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter