archive and purge - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

archive and purge

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2013-10-25 : 14:34:15

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?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-25 : 15:19:37

Look at the OUTPUT function and use it in the insert command to get the information for deleting data from your production table.

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-25 : 15:53:17

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 23:20:10

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources