SQL Server Performance

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

Discussion in 'General Developer Questions' started by walidha, Nov 21, 2006.

  1. walidha New Member

    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.
  2. MohammedU New Member

  3. jezemine New Member

    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
  4. Roji. P. Thomas New Member

  5. FrankKalis Moderator

    quote:Originally posted by Roji. P. Thomas

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

    Roji. P. Thomas

    and is a cause of recompiles.

    Frank Kalis
    Microsoft SQL Server MVP

Share This Page