Post reply

deleting large amout of data

  • March 30, 2003 at 11:44 pm

    #64457

    Hi. Is there any way to easily delete the contents of a whole table (5-6 mil. records). I have tried using MS-Access-ODBC Data Source, but failed (MSSQLServer 2000 crashed - probaly due to buffering overflow).

    matevz

  • March 31, 2003 at 12:44 am

    #452538

    The easiet way to delete the whole of a table is:

    truncate table @tablename

    Jeremy

  • April 1, 2003 at 5:39 am

    #452689

    If deleting the 6 mil records, crashes yor server, you could execute the deletes in parts. You could delete the top 1 mil. for example, and then the next mil.

  • April 1, 2003 at 5:49 am

    #452693

    I regulary truncate a 10 million row table with no problems - takes a couple of seconds.

    Deleting rows with a delete statement is a logged transaction whereas truncate is non-logged. Using delete to delete this many rows will take a long time.

    If you need to reload the table, it might be quicker to drop the indicies, load the data and then recreate the indicies. I found it a lot quicker doing it in this sequence.

    Jeremy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply