SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 archive and purge
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

laddu
Constraint Violating Yak Guru

USA
332 Posts

Posted - 10/25/2013 :  14:34:15  Show Profile  Reply with Quote
Hi, I created stored procedure to archive the orders over 90days and converted sp to run as a sql job. And Job is running fine.
Basically Job will archive the over 90 days old orders from actual production DB to archiveDB
USE [ArchiveDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Orders_to_arch]
AS
SET NOCOUNT ON

SET IDENTITY_INSERT Orders_arch ON

insert into Orders_arch
(
[OrderID],
[Column2],
[column3],

[Column 25]
)
select
O.[OrderID],
O.[Column2],
O.[column3],

O.[Column 25]
from
PROD_DB.dbo.Orders O
left join
Orders_arch oa
on oa.orderid= o.orderid
where
0.process_date < GETDATE() - 90
and oa.orderid is null

SET IDENTITY_INSERT Orders_arch OFF


GO

Now I want to purge the order records from productionDB after archiving them to ArchiveDB. I need to ensure the over 90 days rows are transferred to archive db and then delete from actual prod db. Is there any algorithm to achieve this? is it possible to include purge step also to same archive sql job?



Edited by - laddu on 10/25/2013 14:46:50

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 10/25/2013 :  15:19:37  Show Profile  Reply with Quote
Look at the OUTPUT function and use it in the insert command to get the information for deleting data from your production table.

djj
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/25/2013 :  15:53:17  Show Profile  Reply with Quote
You could also just do the DELETE from production with the 90 day criteria and INNER JOIN to the archiveDB table to insure that you only delete the rows that exist there. You can certainly make that part of the same archive job either by appending the statement after the one you posted or by adding another job step.

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 10/25/2013 :  23:20:10  Show Profile  Reply with Quote

USE [ArchiveDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Orders_to_arch]
AS
SET NOCOUNT ON

SET IDENTITY_INSERT Orders_arch ON

DECLARE @DELETED_ITEMS table
(
[OrderID]  int
)


insert into Orders_arch 
(
[OrderID],
[Column2],
[column3],

[Column 25]
)
OUPUT DELETED.[OrderID],
DELETED.[Column2],
DELETED.[column3],
DELETED.[Column 25]
INTO @DELETED_ITEMS
select
O.[OrderID],
O.[Column2],
O.[column3],

O.[Column 25]
from
PROD_DB.dbo.Orders O
left join
Orders_arch oa 
on oa.orderid= o.orderid
where
0.process_date < GETDATE() - 90
and oa.orderid is null

SET IDENTITY_INSERT Orders_arch OFF

DELETE o
FROM  PROD_DB.dbo.Orders O
INNER JOIN @DELETED_ITEMS d
ON d.OrderID = O.OrderID

GO



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000