Drop and Create or Truncate table

• SueB

  SSCommitted

  Points: 1668

  More actions

  March 18, 2004 at 8:25 am

  #161712

  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: 76000

  More actions

  March 19, 2004 at 4:23 am

  #499461

  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.

  LinkedIn Profile

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  July 19, 2006 at 9:21 am

  #650146

  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.

  thoughts?

  _________________________