x

Script to delete Huge set of records from a table

Hi,

I am facing issue in order to delete a huge number of records from one table where the criteria will deal with single field for different conditions. I am here to attach the query that i have to delete the records with the select statement.

Please provide me with your suggestions..

Thanks in Advance..

Regards, Ramya

SELECT ITEM.ID AS ITEM_ID,
 ORG_COPY.VALUE AS ORG_COPY, 
 LOG_DEL.VALUE AS LOG_DEL, 
 STAT_GNRC.VALUE AS STAT_GNRC, 
 DOC_ID.VALUE AS DOC_ID
    , 
 DOC_OBJ_REL.VALUE AS DOC_OBJ_REL, 
 PAGE_CT.VALUE AS PAGE
 FROM ITEM_DETAIL AS LOG_DEL
 RIGHT OUTER JOIN ITEM ON ITEM.ID = LOG_DEL.ITEM_ID
 AND LOG_DEL.ATTRIBUTE_ID = 'FC0DAAD6-7820-4DA1-AA77-CCF7780038E9' --   LOGICALLY DELETED
 JOIN ITEM_DETAIL (NOLOCK) AS ORG_COPY ON ORG_COPY.ITEM_ID = ITEM.ID
 AND ORG_COPY.ATTRIBUTE_ID = '5F859754-9FC4-4072-A054-41BF40F21A3B' --  ORGANIZATION-COPYRIGHTER (OR PREPARING ACTIVITY)
 JOIN ITEM_DETAIL (NOLOCK) AS STAT_GNRC ON STAT_GNRC.ITEM_ID = ITEM.ID
 AND STAT_GNRC.ATTRIBUTE_ID = 'F103D50E-4BA8-4E72-9AA2-712DB72049B0' -- STATUS-GENERIC
 JOIN ITEM_DETAIL (NOLOCK) AS DOC_ID ON DOC_ID.ITEM_ID = ITEM.ID
 AND DOC_ID.ATTRIBUTE_ID = 'FAA27652-97E8-4FBA-88EB-C77EC82C4109' --    DOC ID
 JOIN ITEM_DETAIL (NOLOCK) AS DOC_OBJ_REL ON DOC_OBJ_REL.ITEM_ID = ITEM.ID
 AND DOC_OBJ_REL.ATTRIBUTE_ID = '33ADEDA2-9BB3-4DB5-A73C-ADAB05D94785' --   DOC OBJECT RELATION
 LEFT OUTER JOIN ITEM_DETAIL (NOLOCK) AS PAGE_CT ON PAGE_CT.ITEM_ID = ITEM.ID
 AND PAGE_CT.ATTRIBUTE_ID = '5216C515-F3CD-41A9-A38A-724FB4AA9D6B' --   PAGE COUNT
 WHERE (LOG_DEL.VALUE IS NULL OR LOG_DEL.VALUE = '0')
 AND (PAGE_CT.VALUE IS NULL OR PAGE_CT.VALUE = '0')
more ▼

asked Jun 01, 2011 at 12:28 AM in Default

Ramya_Basavaraj gravatar image

Ramya_Basavaraj
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
This is the best explanation of how to delete duplicates http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/t-sql-tuesday-016-count-and-delete-duplicates. I would adapt it to only delete a limited number of records so as to not grow your TLog too much and make sure you do regular backups throughout the process.
more ▼

answered Jun 01, 2011 at 12:39 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x52
x10

asked: Jun 01, 2011 at 12:28 AM

Seen: 863 times

Last Updated: Jun 01, 2011 at 12:36 AM