Drop and Create or Truncate table

  • SueB


    Points: 1537

    I am using DTS packages to load data from a text file into a SQL table.  When using the DTS wizard it allows you to drop and create the table prior to loading the data.  Is it better to drop and create the table or just truncate it when loading the new data?

    I have seen this in many procedures and wondered if there was an advantage to using drop/create.

  • David.Poole

    SSC Guru

    Points: 75296

    Firstly you cannot TRUNCATE a table that has primary/foreign key relationships.

    The only advantage to DROP/CREATE that I can see is that it takes care of situations where the source structure has changed since the last time the package was run.

    Personally I prefer to flush out a table and repopulate in preference to DROP/CREATE.

    For big import jobs I tend to follow the procedure below.

    • Drop DRI constraints (if they exist).
    • Drop primary keys.
    • Drop indices.
    • TRUNCATE tables.
    • Import new data
    • Recreate primary keys and indices
    • Recreate DRI (if required).

    By dropping the indices and primary keys it speeds up the import because the server isn't trying to maintain the indices whilst it is at it.

  • neotokyo


    Points: 7239

    excellent idea!! this is awesome!!

    question is... can this be automated via script

    maybe within a 'bulk insert' ??

    so just be sure though... is it possable capture all

    different types of keys prior to drop, and then

    recreate them 'exactly' as they were after the

    import is completed?

    i'm trying to come up with this EXACT thing.

    i'm light on the tsql side though.



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

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