SQL Server Performance

Insert 300 millon of recods

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by LucasDa, Nov 16, 2009.

  1. LucasDa New Member

    Hi...!
    I need to insert in a Table the result of a Query that descrive below:
    SELECT Campo1, Campo2, Campo3 FROM TableUno CROSS JOIN TableDos CROSS JOIN TableTres
    This Query return more than 300 millon of records and I need to put this in another Table. I try to do this with the this Query:
    INSERT INTO TableXXX
    SELECT Campo1, Campo2, Campo3 FROM TableUno CROSS JOIN TableDos CROSS JOIN TableTres
    I have two important problem with this:
    1. The size of the TEMBDB growth from 2 Mb to 500 Gb
    2. the size of the Log growth from 650 Mb to 700 Gb.
    The question is, there are some way to insert 300 millon of records without broken the DB (for example with a cursor or how)???.
    Thank.
    Bye.
  2. ghemant Moderator

    If you break down insertion into smaller chunks this would be easy and you could reduce the burden
  3. satya Moderator

    That is a normal behaviour for TEMPDB & Transaction log to bulge due to the activity.
    Further when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table, you could use SELECT.. INTO.
    ALso you could use BCP to perform the action which will be minimal logged transaction, as Hemant referred keep it in smaller transaction will take care of transaction log if there is a backup job every 15 minutes or even keep the DB in SIMPLE recovery model during this time.

Share This Page