August 14, 2003 at 7:40 am
What is the best way in SQL Server to completely delete the contents of a table.
I need to do complete refreshes of data which I am loading from flat files and Access tables.
Largest table is approx 0.5 million rows.
Using a stored procedure, I first tried below which seemed rather slow:
DELETE FROM tmpEurStat
I then adapted some code on this site:
-- Delete records with every 1000 deletes a commit
declare @loop int, @tel int
select @loop = count(*) / 1000 + 1 from tmpEurStat
set @tel = 0
while @tel < @loop
begin
begin transaction
delete tmpEurStat from (select top 1000 * from tmpEurStat)as T1
commit transaction
set @tel = @tel + 1
end
The third method I tried was to drop and then recreate the table and indexes.
This last method was by far the fastest running. Is this the best way of deleting the contents of a table? Are there any dangers?
August 14, 2003 at 7:45 am
Deleting all contents of a table: I would have thought truncate would be the fastest way as it does not log much in the transaction log, unlike a delete statement:
Truncate table tmpEurStat
....
August 14, 2003 at 7:49 am
Yes, TRUNCATE is faster than DELETE.
Not sure what DROP and (re) CREATE TABLE do
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 14, 2003 at 8:42 am
What can I say? It's so obvious when you see the answer. I have never come across Truncate before.
Thanks
August 15, 2003 at 3:57 am
TRUNCATE also resets the seed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply