June 27, 2017 at 5:33 am
Hi,
I have the Following SP that will be sending the data to archive data before deleting data from tables, I'm trying to figure out what would be the best practice for this to have a while Loop in or another other way that it can be consecutively ran so that it deletes all the data from the table. As I'm currently setting a batch parameter for each table....
Plus if there are any EBI's to this , please let me know
Here is the SP...
CREATE PROCEDURE ArchiveCampaignData
@CampaignId INT,
@DeleteBatchOf INT
AS
BEGIN
--[u_activity_DG]
INSERT INTO dbo.u_activity_archive
SELECT TOP (@DeleteBatchOf)
ID, created , createdBy ,updated ,updatedBy ,integrationId ,recordType ,ownerId ,parId ,parType ,orderId ,orderItemId ,oppId ,serviceId ,workOrderId ,workOrderItemId ,campaignId ,primaryContactId ,parActivityId ,completedById ,schedStartTime ,actualStartTime ,
schedEndTime ,actualEndTime ,unavailableFlag ,calendarFlag ,privacyFlag ,allDayFlag ,showTimeAs ,subject ,location ,reason ,actionType ,actionSubType ,actionStatus ,notes ,ResultCodeId ,dialTime ,talkTime ,
recordedFilePath ,recordedFileName ,outboundCallNumber ,deletedFlag ,recurringFlag ,callBackFlag ,recycledFlag ,hardDeletedFlag ,importId ,attTXT01 ,attTXT02 ,attTXT03 ,attTXT04 ,attTXT05 ,attTXT06 ,attTXT07 ,attTXT08,
attTXT09 ,attTXT10 ,attTXT11 ,attTXT12 ,attTXT13 ,attTXT14 ,attTXT15 ,attTXT16 ,attTXT17 ,attTXT18 ,attTXT19 ,attTXT20 ,attTXT21 ,attTXT22 ,attTXT23 ,attTXT24 ,attTXT25 ,attTXT26 ,attTXT27 ,attTXT28 ,
attTXT29 ,attTXT30 ,attTXT31 ,attTXT32 ,attTXT33 ,attTXT34 ,attTXT35 ,attTXT36 ,attTXT37 ,attTXT38 ,attTXT39 ,attTXT40 ,attTXT41 ,attTXT42 ,attTXT43 ,attTXT44 ,attTXT45 ,attTXT46 ,attTXT47 ,
attTXT48 ,attTXT49 ,attTXT50 ,attTXT51 ,attTXT52 ,attTXT53 ,attTXT54 ,attTXT55 ,attTXT56 ,attTXT57 ,attTXT58 ,attTXT59 ,attTXT60 ,attTXT61 ,attTXT62 ,attTXT63 ,attTXT64 ,attTXT65 ,attTXT66 ,attTXT67 ,attTXT68 ,
attTXT69 ,attTXT70 ,attDT01 ,attDT02 ,attDT03 ,attDT04 ,attDT05 ,attDT06 ,attDT07 ,attDT08 ,attDT09 ,attDT10 ,attDT11 ,attDT12 ,attDT13 ,attDT14 ,attDT15 ,attDT16 ,
attDT17 ,attDT18 ,attDT19 ,attDT20 ,attNum01 ,attNum02 ,attNum03 ,attNum04 ,attNum05 ,attNum06 ,attNum07 ,attNum08 ,attNum09 ,attNum10 ,attNum11 ,attNum12 ,attNum13 ,attNum14 ,attNum15 ,attNum16 ,
attNum17 ,attNum18 ,attNum19 ,attNum20 ,attNum21 ,attNum22 ,attNum23 ,attNum24 ,attNum25 ,attNum26 ,attNum27 ,attNum28 ,attNum29 ,attNum30 ,attBIT01 ,attBIT02 ,attBIT03 ,attBIT04 ,attBIT05 ,
attBIT06 ,attBIT07 ,attBIT08 ,attBIT09 ,attBIT10 ,attINT01 ,attINT02 ,attINT03 ,attINT04 ,attINT05 ,attINT06 ,attINT07 ,attINT08 ,attINT09 ,attINT10 ,ddi ,incomingNumber ,callId ,ActivityDuration ,caseId,1
FROM dbo.u_activity
WHERE campaignId = @CampaignID
--campaignId IN (767,798,796)
DELETE FROM dbo.u_activity
WHERE ID IN ( SELECT ID FROM dbo.u_activity_archive WHERE deleted = 1 )
UPDATE u_activity_archive
SET deleted = 2
WHERE deleted = 1
--============================================================================================================================
--[u_contact_DG]
INSERT INTO dbo.u_contact_archive
SELECT TOP(@DeleteBatchOf)
u_Contact.id,u_Contact.created ,u_Contact.createdBy ,u_Contact.updated ,u_Contact.updatedBy ,exportedToDialler ,u_Contact.importId ,integrationId ,recordType ,recordSubType ,parId ,ownerId ,primaryContactId ,deletedFlag ,salutation ,name ,otherName ,initial ,jobTitle ,addr1 ,addr2 ,addr3 ,
addr4 ,city ,county ,postCode ,country ,dob ,phone1 ,phone2 ,phone3 ,fax ,login ,password ,email ,startDate ,expiryDate ,nationality ,ethnicOrigin ,optIn ,source ,source2 ,gender ,maritalStatus ,
homeOwner ,havechildren ,employmentStatus ,isRefferral ,residentialStatus ,niNumber ,notes ,url ,region ,u_Contact.recycledFlag ,u_Contact.hardDeletedFlag ,attTXT01 ,attTXT02 ,attTXT03 ,attTXT04 ,attTXT05 ,attTXT06 ,
attTXT07 ,attTXT08 ,attTXT09 ,attTXT10 ,attTXT11 ,attTXT12 ,attTXT13 ,attTXT14 ,attTXT15 ,attTXT16 ,attTXT17 ,attTXT18 ,attTXT19 ,attTXT20 ,attTXT21 ,attTXT22 ,attTXT23 ,attTXT24 ,attTXT25 ,attTXT26 ,
attTXT27 ,attTXT28 ,attTXT29 ,attTXT30 ,attTXT31 ,attTXT32 ,attTXT33 ,attTXT34 ,attTXT35 ,attTXT36 ,attTXT37 ,attTXT38 ,attTXT39 ,attTXT40 ,attTXT41 ,
attTXT42 ,attTXT43 ,attTXT44 ,attTXT45 ,attTXT46 ,attTXT47 ,attTXT48 ,attTXT49 ,attTXT50 ,attTXT51 ,attTXT52 ,attTXT53 ,attTXT54 ,attTXT55 ,attTXT56 ,attTXT57 ,attTXT58 ,attTXT59 ,attTXT60 ,
attTXT61 ,attTXT62 ,attTXT63 ,attTXT64 ,attTXT65 ,attTXT66 ,attTXT67 ,attTXT68 ,attTXT69 ,attTXT70 ,attTXT71 ,attTXT72 ,attTXT73 ,attTXT74 ,attTXT75 ,attTXT76 ,attTXT77 ,attTXT78 ,attDT01 ,attDT02 ,attDT03 ,
attDT04 ,attDT05 ,attDT06 ,attDT07 ,attDT08 ,attDT09 ,attDT10 ,attDT11 ,attDT12 ,attDT13 ,attDT14 ,attDT15 ,attDT16 ,attDT17 ,attDT18 ,attDT19 ,attDT20 ,attNum01 ,attNum02 ,attNum03 ,attNum04 ,
attNum05 ,attNum06 ,attNum07 ,attNum08 ,attNum09 ,attNum10 ,attNum11 ,attNum12 ,attNum13 ,attNum14 ,attNum15 ,attNum16 ,attNum17 ,attNum18 ,attNum19 ,attNum20 ,attNum21 ,attNum22 ,attNum23 ,attNum24 ,
attNum25 ,attNum26 ,attNum27 ,attNum28 ,attNum29, attNum30 ,attBIT01 ,attBIT02 ,attBIT03 ,attBIT04 ,attBIT05 ,attBIT06 ,attBIT07 ,attBIT08 ,attBIT09 ,attBIT10 ,attINT01 ,attINT02 ,attINT03 ,
attINT04 ,attINT05 ,attINT06 ,attINT07 ,attINT08 ,attINT09 ,attINT10 ,attLRGTXT661 ,attLRGTXT671 ,attLRGTXT681 ,attLRGTXT691 ,attLRGTXT701 ,attLRGTXT702 ,attLRGTXT703 ,attLRGTXT704 ,attLRGTXT705 ,
attLRGTXT706 ,attLRGTXT707 ,attLRGTXT708 ,attLRGTXT709 ,attLRGTXT710 ,attLRGTXT711 ,attLRGTXT712 ,attLRGTXT713 ,attLRGTXT714 ,attLRGTXT715 ,attLRGTXT716 ,attLRGTXT717 ,attLRGTXT718 ,attLRGTXT719 ,attLRGTXT720 ,
attBIT11 ,attBIT12 ,attBIT13 ,attBIT14 ,attBIT15 ,attBIT16 ,attBIT17 ,attBIT18 ,attBIT19 ,attBIT20 ,attBIT21 ,attBIT22 ,attBIT23 ,attBIT24 ,attBIT25 ,attBIT26 ,attBIT27 ,attBIT28 ,attBIT29 ,
attBIT30 ,attTXT79 ,attTXT80 ,attTXT81 ,attTXT82 ,attTXT83 ,attTXT84 ,attTXT85 ,attTXT86 ,attTXT87 ,attTXT88 ,currentSaleValue ,newSaleValue ,saleValueFactor ,addrValidated ,addrValidationAttempted ,
addrNoAddress ,attTXT89 ,attTXT90 ,attTXT91 ,attTXT92 ,attTXT93 ,attTXT94 ,attTXT95 ,attTXT96 ,attTXT97 ,attTXT98 ,attTXT99 ,attTXT100 ,attBIT31 ,attBIT32 ,attBIT33 ,attBIT34 ,attBIT35 ,attBIT36 ,attBIT37 ,attBIT38 ,attBIT39 ,
attBIT40 ,attBIT41 ,attBIT42 ,attBIT43 ,attBIT44 ,attBIT45 ,attBIT46 ,attBIT47 ,attBIT48 ,attBIT49 ,attBIT50 ,attINT11 ,attINT12 ,attINT13 ,attINT14 ,attINT15 ,attINT16 ,attINT17 ,attINT18 ,
attINT19 ,attINT20 ,attINT21 ,attINT22 ,attINT23 ,attINT24 ,attINT25 ,ExportId ,allowLoginFromAnywhere ,attDT21 ,attDT22 ,attDT23 ,attDT24 ,attDT25 ,attDT26 ,attDT27 ,activeEmailId ,categoryId,1
FROM U_contact WITH(NOLOCK)
INNER JOIN dbo.u_campaignContact WITH(NOLOCK) ON u_contact.id = u_campaigncontact.contactId
WHERE campaignId = @CampaignId
--campaignid in (767,798,796)
DELETE FROM u_Contact
WHERE ID IN ( SELECT ID FROM U_Contact_archive WHERE deleted = 1)
UPDATE u_Contact_archive
SET deleted = 2
WHERE deleted = 1
--============================================================================================================================
--[u_campaignContact_DG]
INSERT INTO dbo.u_campaignContact_archive
SELECT TOP(@DeleteBatchOf)
ID,created ,createdBy ,updated ,updatedBy ,campaignId ,contactId ,resultCodeId ,callCount ,noAnswerCount ,lockedById ,active ,awaitingCallBack ,weighting ,recycledFlag ,hardDeletedFlag ,importId ,
deadLineCount ,unlockedCount ,suppressFlag ,resetToDXIActivityId,1
FROM dbo.u_campaignContact WITH(NOLOCK)
WHERE campaignId = @CampaignId
--campaignId IN (767,798,796)
DELETE FROM u_campaignContact
WHERE ID IN (SELECT ID FROM u_campaignContact_archive WHERE deleted = 1)
UPDATE u_campaignContact_archive
SET deleted = 2
WHERE id = 1
--============================================================================================================================
--- DXI CDR LOG DG
INSERT INTO dbo.dxi_CDRLog_archive
SELECT TOP (@DeleteBatchOf)
dbo.dxi_CDRLog.id, dbo.dxi_CDRLog.created ,callid ,qid ,dataset ,urn ,agent ,ddi ,cli ,ringtime ,duration ,result ,outcome ,type ,datetime ,answer ,disconnect ,last_update ,carrier ,flags ,terminate ,customer_cost ,dial_code,1
FROM dbo.dxi_CDRLog
INNER JOIN dbo.u_campaignHdr WITH (NOLOCK) ON u_campaignHdr.diallerCampaignId = dxi_CDRLog.qid
WHERE dbo.u_campaignHdr.parentId = @CampaignId
DELETE FROM dbo.dxi_CDRLog
WHERE id IN (SELECT id FROM dxi_CDRLog_archive WHERE deleted = 1 )
UPDATE dxi_CDRLog_archive
SET DELETED = 2
WHERE ID = 1
--============================================================================================================================
--DiallerstatSmall DG
INSERT INTO dbo.[dxi_diallerStatsSmall_archive]
SELECT TOP (@DeleteBatchOf)
dxi_diallerStatsSmall.id,dxi_diallerStatsSmall.created ,dxi_diallerStatsSmall.updated ,datetime ,callid ,campaign ,wait ,wrap,1
FROM dbo.dxi_diallerStatsSmall
INNER JOIN dbo.u_campaignHdr WITH (NOLOCK) ON u_campaignHdr.diallerCampaignId = dxi_diallerStatsSmall.campaign
WHERE dbo.u_campaignHdr.parentId = @CampaignId
--campaign IN (16528,64039,64040,64041,64042,64733,64734,16614,64738,16615,64775)
DELETE FROM dbo.dxi_diallerStatsSmall
WHERE ID IN (SELECT ID FROM dbo.dxi_diallerStatsSmall WHERE DELETED = 1)
UPDATE dbo.dxi_diallerStatsSmall_archive
SET deleted = 2
WHERE id = 1
--============================================================================================================================
--u_campaignHdr
INSERT INTO dbo.u_campaignHdr_archive
SELECT TOP(@DeleteBatchOf)
id,created ,createdBy ,updated ,updatedBy ,parentId ,name ,startDate ,endDate ,description ,status ,scriptId ,
clientId ,requiresProducts ,salesMethodology ,callRecordRoot ,campaignType ,diallerType ,targetChartId ,clientLogo ,diallerCampaignId ,diallerCampaignName ,assertCLI ,recycledFlag ,hardDeletedFlag ,
importId ,attTXT01 ,attTXT02 ,attTXT03 ,attTXT04 ,attTXT05 ,attTXT06 ,attTXT07 ,attTXT08 ,attTXT09 ,attTXT10 ,attTXT50 ,attTXT51 ,attTXT52 ,attTXT53 ,attTXT54 ,attTXT55 ,attTXT56 ,attTXT57 ,
attTXT58 ,attTXT59 ,attTXT60 ,attTXT61 ,attTXT62 ,attTXT63 ,attTXT64 ,attTXT65 ,attDT01 ,attDT02 ,attDT03 ,attDT04 ,attDT05 ,attDT06 ,attDT07 ,attDT08 ,attDT09 ,attDT10 ,attNum01 ,attNum02 ,
attNum03 ,attNum04 ,attNum05 ,attNum06 ,attNum07 ,attNum08 ,attNum09 ,attNum10 ,attNum11 ,attNum12 ,attNum13 ,attNum14 ,attNum15 ,attNum16 ,attNum17 ,attNum18 ,attNum19 ,attNum20 ,attNum21 ,
attNum22 ,attNum23 ,attNum24 ,attNum25 ,attNum26 ,attNum27 ,attNum28 ,attNum29 ,attNum30 ,attBIT01 ,attBIT02 ,attBIT03 ,attBIT04 ,attBIT05 ,attBIT06 ,attBIT07 ,attBIT08 ,attBIT09 ,attBIT10 ,
attINT01 ,attINT02 ,attINT03 ,attINT04 ,attINT05 ,attINT06 ,attINT07 ,attINT08 ,attINT09 ,attINT10 ,allowImmediateCB ,attINT11 ,attINT12 ,attBit11 ,attBit12 ,attBit13 ,hideCallHistory ,
attINT13 ,attINT14 ,attINT15 ,attINT16 ,attINT17 ,attINT18 ,attINT19 ,attINT20 ,attINT21 ,attINT22 ,attINT23 ,attINT24 ,attINT25 ,attINT26 ,attINT27 ,customFormUrl ,customSearchFormUrl,1
FROM dbo.u_campaignHdr
WHERE Id = @CampaignId OR parentId = @CampaignId
--IN (767,768,769,770,771,794,795,796,797,798,800)
DELETE FROM dbo.u_campaignHdr
WHERE id IN ( SELECT id FROM u_campaignhdr_archive WHERE deleted = 1)
UPDATE u_campaignhdr_archive
SET deleted = 2
WHERE deleted = 1
END
June 27, 2017 at 7:22 am
But the Output would only be good for returning errors ?
June 27, 2017 at 7:28 am
Suth - Tuesday, June 27, 2017 7:22 AMBut the Output would only be good for returning errors ?
No, he means the Output clause.
https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql
June 27, 2017 at 8:11 am
Eirikur Eiriksson - Tuesday, June 27, 2017 5:48 AM
I've tried to go about it with the following...
DELETE FROM dbo.u_activity
OUTPUT DELETED.* INTO u_activity_archive
WHERE ID = 12
but I'm getting a error
Msg 213, Level 16, State 1, Line 13
Column name or number of supplied values does not match table definition.
June 27, 2017 at 8:17 am
Suth - Tuesday, June 27, 2017 8:11 AMEirikur Eiriksson - Tuesday, June 27, 2017 5:48 AMI've tried to go about it with the following...
DELETE FROM dbo.u_activity
OUTPUT DELETED.* INTO u_activity_archive
WHERE ID = 12but I'm getting a error
Msg 213, Level 16, State 1, Line 13
Column name or number of supplied values does not match table definition.
Don't output *, instead specify the columns. You may need to add some data as literals to the output if the table has more columns than values.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2017 at 1:47 am
For cases when I have the Inner Join the Output will be no good ? as I'm unable to Join back
Unless there is a different way about going about this ?
DELETE TOP(@DeleteBatchOf) FROM dbo.dxi_CDRLog
INNER JOIN dbo.u_campaignHdr WITH (NOLOCK) ON u_campaignHdr.diallerCampaignId = dxi_CDRLog.qid
OUTPUT DELETED,* INTO dxi_CDRLog_archive
WHERE dbo.u_campaignHdr.parentId = @CampaignId
June 28, 2017 at 3:16 pm
Suth - Wednesday, June 28, 2017 1:47 AMFor cases when I have the Inner Join the Output will be no good ? as I'm unable to Join backUnless there is a different way about going about this ?
DELETE TOP(@DeleteBatchOf) FROM dbo.dxi_CDRLog
INNER JOIN dbo.u_campaignHdr WITH (NOLOCK) ON u_campaignHdr.diallerCampaignId = dxi_CDRLog.qid
OUTPUT DELETED,* INTO dxi_CDRLog_archive
WHERE dbo.u_campaignHdr.parentId = @CampaignId
I'm not sure what you mean. I'm going to reformat your DELETE statement and add table aliases and correct a syntax issue and see if that helps clarify what you're actually seeking to accomplish:DELETE TOP (@DeleteBatchOf) L
OUTPUT DELETED.* INTO dbo.dxi_CDRLogArchive
FROM dbo.dxi_CDRLog AS L
INNER JOIN dbo.u_campaignHdr AS H WITH(NOLOCK)
ON L.qid = H.diallerCampaignId
WHERE H.parentId = @CampaignId;
Let me know if your objective is solely to delete records from the dxi_CDRLog table or is something else.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 3, 2017 at 9:08 am
sgmunson - Wednesday, June 28, 2017 3:16 PMSuth - Wednesday, June 28, 2017 1:47 AMFor cases when I have the Inner Join the Output will be no good ? as I'm unable to Join backUnless there is a different way about going about this ?
DELETE TOP(@DeleteBatchOf) FROM dbo.dxi_CDRLog
INNER JOIN dbo.u_campaignHdr WITH (NOLOCK) ON u_campaignHdr.diallerCampaignId = dxi_CDRLog.qid
OUTPUT DELETED,* INTO dxi_CDRLog_archive
WHERE dbo.u_campaignHdr.parentId = @CampaignIdI'm not sure what you mean. I'm going to reformat your DELETE statement and add table aliases and correct a syntax issue and see if that helps clarify what you're actually seeking to accomplish:
DELETE TOP (@DeleteBatchOf) L
OUTPUT DELETED.* INTO dbo.dxi_CDRLogArchive
FROM dbo.dxi_CDRLog AS L
INNER JOIN dbo.u_campaignHdr AS H WITH(NOLOCK)
ON L.qid = H.diallerCampaignId
WHERE H.parentId = @CampaignId;Let me know if your objective is solely to delete records from the dxi_CDRLog table or is something else.
Thanks , yeah the goal is to delete the data from the table
July 3, 2017 at 9:12 am
CREATE PROCEDURE ArchiveCampaignData
@CampaignId INT,
@DeleteBatchOf INT
AS
BEGIN
DELETE TOP (@DeleteBatchOf)FROM dbo.u_activity
OUTPUT DELETED.* INTO u_activity_archive
WHERE campaignId = @CampaignId
--============================================================================================================================
DELETE TOP (@DeleteBatchOf) C
OUTPUT DELETED.* INTO dbo.u_contact_archive
FROM dbo.U_contact AS C
INNER JOIN dbo.U_Campaigncontact AS CON WITH(NOLOCK)ON C.id = CON.contactId
WHERE CON.campaignId = @CampaignId
--============================================================================================================================
DELETE TOP (@DeleteBatchOf) FROM u_campaignContact
OUTPUT Deleted.* INTO u_campaignContact_archive
WHERE campaignId = @CampaignId
--============================================================================================================================
DELETE TOP (@DeleteBatchOf) L
OUTPUT DELETED.* INTO dbo.dxi_CDRLog_Archive
FROM dbo.dxi_CDRLog AS L
INNER JOIN dbo.u_campaignHdr AS H WITH(NOLOCK)ON L.qid = H.diallerCampaignId
WHERE H.parentId = @CampaignId
--============================================================================================================================
DELETE TOP(@DeleteBatchOf) T
OUTPUT Deleted.* INTO dxi_diallerStatsSmall_archive
FROM dbo.dxi_diallerStatsSmall AS T
INNER JOIN dbo.u_campaignHdr AS G WITH (NOLOCK) ON G.diallerCampaignId = T.campaign
WHERE G.parentId = @CampaignId
--============================================================================================================================
DELETE TOP(@DeleteBatchOf) FROM dbo.u_campaignHdr
OUTPUT Deleted.* INTO u_campaignHdr_archive
WHERE Id = @CampaignId OR parentId = @CampaignId
END
What would be the best way looping each transaction ? so that it doesn't go into a infinity loop ?
I have come up with this ... But would this be the best way to go about it ?
CREATE PROCEDURE ArchiveCampaignData
@CampaignId INT,
@DeleteBatchOf INT
AS
BEGIN
DECLARE @Rowcount INT = 10
WHILE @Rowcount > 0
BEGIN --[u_activity_archive]
DELETE TOP (@DeleteBatchOf)FROM dbo.u_activity
OUTPUT DELETED.* INTO u_activity_archive
WHERE campaignId = @CampaignId
SET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --[u_contact_archive]
WHILE @Rowcount > 0
DELETE TOP (@DeleteBatchOf) C
OUTPUT DELETED.* INTO dbo.u_contact_archive
FROM dbo.U_contact AS C
INNER JOIN dbo.U_Campaigncontact AS CON WITH(NOLOCK)ON C.id = CON.contactId
WHERE CON.campaignId = @CampaignId
SET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --[u_campaignContact_archive]
WHILE @Rowcount > 0
DELETE TOP (@DeleteBatchOf) FROM u_campaignContact
OUTPUT Deleted.* INTO u_campaignContact_archive
WHERE campaignId = @CampaignId
SET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --- DXI CDR LOG Archive
WHILE @Rowcount > 0
DELETE TOP (@DeleteBatchOf) L
OUTPUT DELETED.* INTO dbo.dxi_CDRLog_Archive
FROM dbo.dxi_CDRLog AS L
INNER JOIN dbo.u_campaignHdr AS H WITH(NOLOCK)ON L.qid = H.diallerCampaignId
WHERE H.parentId = @CampaignId
SET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --DiallerstatSmall Archive
WHILE @Rowcount > 0
DELETE TOP(@DeleteBatchOf) T
OUTPUT Deleted.* INTO dxi_diallerStatsSmall_archive
FROM dbo.dxi_diallerStatsSmall AS T
INNER JOIN dbo.u_campaignHdr AS G WITH (NOLOCK) ON G.diallerCampaignId = T.campaign
WHERE G.id = @CampaignId
SET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --u_campaignHdr archive
WHILE @Rowcount > 0
DELETE TOP(@DeleteBatchOf) FROM dbo.u_campaignHdr
OUTPUT Deleted.* INTO u_campaignHdr_archive
WHERE Id = @CampaignId OR parentId = @CampaignId
SET @Rowcount = @@ROWCOUNT
END
END
July 7, 2017 at 8:12 am
Suth - Monday, July 3, 2017 9:12 AMCREATE PROCEDURE ArchiveCampaignData
@CampaignId INT,
@DeleteBatchOf INTAS
BEGIN
DELETE TOP (@DeleteBatchOf)FROM dbo.u_activity
OUTPUT DELETED.* INTO u_activity_archive
WHERE campaignId = @CampaignId--============================================================================================================================
DELETE TOP (@DeleteBatchOf) C
OUTPUT DELETED.* INTO dbo.u_contact_archive
FROM dbo.U_contact AS C
INNER JOIN dbo.U_Campaigncontact AS CON WITH(NOLOCK)ON C.id = CON.contactId
WHERE CON.campaignId = @CampaignId--============================================================================================================================
DELETE TOP (@DeleteBatchOf) FROM u_campaignContact
OUTPUT Deleted.* INTO u_campaignContact_archive
WHERE campaignId = @CampaignId--============================================================================================================================
DELETE TOP (@DeleteBatchOf) L
OUTPUT DELETED.* INTO dbo.dxi_CDRLog_Archive
FROM dbo.dxi_CDRLog AS L
INNER JOIN dbo.u_campaignHdr AS H WITH(NOLOCK)ON L.qid = H.diallerCampaignId
WHERE H.parentId = @CampaignId
--============================================================================================================================
DELETE TOP(@DeleteBatchOf) T
OUTPUT Deleted.* INTO dxi_diallerStatsSmall_archive
FROM dbo.dxi_diallerStatsSmall AS T
INNER JOIN dbo.u_campaignHdr AS G WITH (NOLOCK) ON G.diallerCampaignId = T.campaign
WHERE G.parentId = @CampaignId
--============================================================================================================================
DELETE TOP(@DeleteBatchOf) FROM dbo.u_campaignHdr
OUTPUT Deleted.* INTO u_campaignHdr_archive
WHERE Id = @CampaignId OR parentId = @CampaignIdEND
What would be the best way looping each transaction ? so that it doesn't go into a infinity loop ?
I have come up with this ... But would this be the best way to go about it ?
CREATE PROCEDURE ArchiveCampaignData
@CampaignId INT,
@DeleteBatchOf INTAS
BEGIN
DECLARE @Rowcount INT = 10
WHILE @Rowcount > 0BEGIN --[u_activity_archive]
DELETE TOP (@DeleteBatchOf)FROM dbo.u_activity
OUTPUT DELETED.* INTO u_activity_archive
WHERE campaignId = @CampaignIdSET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================BEGIN --[u_contact_archive]
WHILE @Rowcount > 0DELETE TOP (@DeleteBatchOf) C
OUTPUT DELETED.* INTO dbo.u_contact_archive
FROM dbo.U_contact AS C
INNER JOIN dbo.U_Campaigncontact AS CON WITH(NOLOCK)ON C.id = CON.contactId
WHERE CON.campaignId = @CampaignIdSET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --[u_campaignContact_archive]WHILE @Rowcount > 0
DELETE TOP (@DeleteBatchOf) FROM u_campaignContact
OUTPUT Deleted.* INTO u_campaignContact_archive
WHERE campaignId = @CampaignIdSET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --- DXI CDR LOG Archive
WHILE @Rowcount > 0DELETE TOP (@DeleteBatchOf) L
OUTPUT DELETED.* INTO dbo.dxi_CDRLog_Archive
FROM dbo.dxi_CDRLog AS L
INNER JOIN dbo.u_campaignHdr AS H WITH(NOLOCK)ON L.qid = H.diallerCampaignId
WHERE H.parentId = @CampaignIdSET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --DiallerstatSmall Archive
WHILE @Rowcount > 0DELETE TOP(@DeleteBatchOf) T
OUTPUT Deleted.* INTO dxi_diallerStatsSmall_archive
FROM dbo.dxi_diallerStatsSmall AS T
INNER JOIN dbo.u_campaignHdr AS G WITH (NOLOCK) ON G.diallerCampaignId = T.campaign
WHERE G.id = @CampaignIdSET @Rowcount = @@ROWCOUNT
END
--============================================================================================================================
BEGIN --u_campaignHdr archive
WHILE @Rowcount > 0DELETE TOP(@DeleteBatchOf) FROM dbo.u_campaignHdr
OUTPUT Deleted.* INTO u_campaignHdr_archive
WHERE Id = @CampaignId OR parentId = @CampaignIdSET @Rowcount = @@ROWCOUNT
END
END
That NOLOCK hint in this is a recipe for disaster. It is not a magic "go faster" button that doesn't have some potentially serious ramifications. It may be ok in this situation since you are looping but it is definitely possible that it will not delete all the rows you expect because when using that hint you can get missing and/or duplicate rows. In your case the duplicate would not be a big concern but if it skips a row it may actually skip some data that you want deleted. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/
Also, your code is not going to work the way you want it to. You are looping until no rows are affected in the outer and inner loops using the same counter. So once the last loop no longer affects any rows the variable @Rowcount will be set to 0. As such, after the first batch processed this will exit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply