"Select..Into" VS "Insert Into..Select"

Can someone please tell me which is a better statement to use, performancewise, "Select..Into" or "Insert Into..Select"? I am selecting about 67million rows. I also need to know if there is a way in either statement, or maybe another mehtod, to create the destination table without logging the transactions and fill the log files. I cannot change the database logging methods to "Simple". So I really appreciate it if someone know best practice for this opertion. Thank you.

What is the recovery model of your database?

SELECT INTO is non-logged operation when the db is in bulk_logged mode.
You can use BCP too...

Read the the following article for more info.

http://support.microsoft.com/?scid=kb;en-us;Q272093

Mohammed.

I would use neither of these methods - bulk export and load would be much faster.

use bcp.exe to export, then again to import rows into the new table. drop indexes on the dest table before you do the insert, recreate them afterward.

bcp.exe MyDatabase.dbo.SourceTable out allrows.txt -c -T -SSERVER
bcp.exe MyDatabase.dbo.DestTable in allrows.txt -c -T -SSERVER

SqlSpec - a fast, cheap, and comprehensive data dictionary generator
for SQL Server 2000 and 2005 and Analysis Server 2005 - www.elsasoft.org

INSERT INTO is recommended over SELECT INTO since the later requires more lock resources.

Roji. P. Thomas
http://toponewithties.blogspot.com