Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:

> They are effectively the same, but dropping the table in the right order i
s
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.

> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lo
t
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.

> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
>
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
>
the[vbcol=seagreen]
lot[vbcol=seagreen]
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
>|||Uri Dimant wrote:

> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( Fir
st
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.

>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>
> is
>
> the
>
> lot
>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.