delete rows

Question

delete rows

riya_dave

Hall of Fame

Points: 3106
More actions
July 16, 2013 at 12:36 pm

#280532

hi
i need to move data to another table and then remove from old one ,which i needs to delete 30 millions of rows,
i know its taking time but any idea i can do it faster like in a batch

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You should do it in several batches. If you give us the code you're using, we might be able to help more. A simple example could be this.

SELECT 1

WHILE @@ROWCOUNT > 0

BEGIN

DELETE TOP (1000000)

FROM MyTable

WHERE MyCondition = 'Delete this row'

END

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101134 More actions · Answer 2

If you can, instead just truncate the old table, then re-insert only the kept rows.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sampath66999 Newbie Points: 7 More actions · Answer 3

copy the table to another table and then truncate the old table

Voide Mr or Mrs. 500 Points: 548 More actions · Answer 4

Before modifying data it is always a good idea to create a temp table as a backup. Once you delete it all, there's no going back.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Hi Riya, its better if you keep of your old data in some temp table and then insert the new data....

first insert the old data into a temp table then truncate old table and insert new ones into that....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

riya_dave (7/16/2013)
hi
i need to move data to another table and then remove from old one ,which i needs to delete 30 millions of rows,
i know its taking time but any idea i can do it faster like in a batch

After deleting 30 million rows from the old table, how many rows are left?

How similar are the two tables?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

riya_dave Hall of Fame Points: 3106 More actions · Answer 7

total i have 60 millions rows from there i need to insert 30 millions to some another table.

once it is insert i need to delete it from original table

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

riya_dave (7/17/2013)
total i have 60 millions rows from there i need to insert 30 millions to some another table.
once it is insert i need to delete it from original table

You can do the delete/insert in the same statement like so:

DELETE TOP (100000) FROM LiveTable

OUTPUT deleted.* INTO ArchiveTable

WHERE ...

--batched up so it doesn't choke up your resources.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden