best solution to drop and create tables 2

[luzippu]

hi,

i need to be able to "drop and recreate" some tables in an sql server 7 enviroment as fast as possible.

currently i have a simple dts that deletes data on existing tables, then other dts's that insert new data back into this tables.

due to size and indexes, the delete process takes a while to complete.

is a 'drop table' and 'create table' approch a better solution? if so, i'd like some expert guidance on the script for the sp or dts.

thanks a lot.
luzippu

 

[gmmastros]

If you want to delete ALL the data in a table, you could truncate it instead of deleting all the data. Truncating a table deletes all the data, but it's not logged in the transaction log in the same way that delete it. Truncate is MANY times faster than deleting all the data.

Truncate Table [!]TableName[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SimonSellick]

Hi,

If you don't need the table data, TRUNCATE is a bit less drastic than dropping the table, and it doesn't destroy the indexes and other constraints.

If you do need some of the data, you might be able to speed up the delete and insert by dropping and re-creating indexes during the process; or by reorganising any problem indexes before and possibly after the process ('problem' indexes are likely to be based on columns that arrive with many similar values in your insert process).

You can code a truncate table as a simple SQL task in the DTS pacakage.

HTH

 

[SQLDenis]

Here we go
Super fast way to truncate or delete
Remember, if there are foreign keys you have to delete and can not truncate ;-)

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else 
  TRUNCATE TABLE ?
'
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Super-fast way to truncate or delete ALL user tables, right? .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

yes ALL not SOME/ANY

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SimonSellick]

How about

 DROP DATABASE

?

 

[luzippu]

thanks all for your input.

i've tested the truncate option and it does the job exactly how i need it and very fast.

i surely be in touch again before the end of this project.
ttfn

luzippu