Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

delete rows Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 12:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 4, 2013 8:13 AM
Points: 190, Visits: 367
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
Post #1474255
Posted Tuesday, July 16, 2013 12:58 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 4,046, Visits: 9,205
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474258
Posted Tuesday, July 16, 2013 2:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:47 PM
Points: 2,330, Visits: 3,510
If you can, instead just truncate the old table, then re-insert only the kept rows.

SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1474287
Posted Tuesday, July 16, 2013 4:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 8:09 AM
Points: 1, Visits: 6
copy the table to another table and then truncate the old table
Post #1474323
Posted Tuesday, July 16, 2013 4:27 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 3:18 PM
Points: 30, Visits: 280
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.
Post #1474334
Posted Wednesday, July 17, 2013 12:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1474393
Posted Wednesday, July 17, 2013 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1474417
Posted Wednesday, July 17, 2013 8:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 4, 2013 8:13 AM
Points: 190, Visits: 367
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
Post #1474641
Posted Wednesday, July 17, 2013 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1474659
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse