Purging Data to history database (Simple and short code)

Question

I need to create a stored proc which to move the data in certain table in Production database which before 2010 to Archive database (created with the same structure as Production). The method I used is Copy the data from Production to Archive and Delete data in Production where exists in Archive. Here is the code:

--## INSERT data from Production DB to Archive DB

    --tableMaster
    INSERT INTO [DB_Archive].[dbo].[tableMaster]
    select * from [DB_Production].[dbo].[tableMaster]
    where QuoDate < '2010-01-01 00:00:00.000'

    --tableDetail
    INSERT INTO [DB_Archive].[dbo].[tableDetail]
    select * from [DB_Production].[dbo].[tableDetail] tblDet
    where exists (select tblMaster.QuoNo,tblMaster.AgentCode from [DB_Archive].[dbo].[tableMaster] tblMaster
                                                    where tblDet.QuoNo = tblMaster.QuoNo)

    --tableSubDetail1
    INSERT INTO [DB_Archive].[dbo].[tableSubDetail1]
    select * from [DB_Production].[dbo].[tableSubDetail1] tblsub1
    where exists (select tblMaster.QuoNo,tblMaster.AgentCode from [DB_Archive].[dbo].[tableMaster] tblMaster 
                                                    where tblsub1.QuoNo = tblMaster.QuoNo)

    --tableSubDetail2
    INSERT INTO [DB_Archive].[dbo].[tableSubDetail2]
    select * from [DB_Production].[dbo].[tableSubDetail2] tblsub2
    where exists (select tblMaster.QuoNo,tblMaster.AgentCode from [DB_Archive].[dbo].[tableMaster] tblMaster 
                                                    where tblsub2.QuoNo = tblMaster.QuoNo)


    --## DELETE data from Production DB

    --tableMaster
    DELETE tblM FROM [DB_Archive].[dbo].[tableMaster] tblM
    Where exists (select bmas.QuoNo from [DB_Archive].[dbo].[tableMaster] bmas 
                                        where tblM.QuoNo = bmas.QuoNo)

    --tableDetail
    DELETE tblD FROM [DB_Archive].[dbo].[tableDetail] tblD
    Where exists (select bmas.QuoNo from [DB_Archive].[dbo].[tableDetail] bmas 
                                        where tblM.QuoNo = bmas.QuoNo)

    --tableSubDetail1
    DELETE tblS1 FROM [DB_Archive].[dbo].[tableSubDetail1] tblS1
    Where exists (select bmas.QuoNo from [DB_Archive].[dbo].[tableSubDetail1] bmas 
                                        where tblM.QuoNo = bmas.QuoNo)

    --tableSubDetail2
    DELETE tblS2 FROM [DB_Archive].[dbo].[tableSubDetail2] tblS2
    Where exists (select bmas.QuoNo from [DB_Archive].[dbo].[tableSubDetail2] bmas 
                                        where tblM.QuoNo = bmas.QuoNo)

Above is for only 4 table and I have 30++ table to go with the same script. Can you advise me how to make this code simple and shorter? Thank you

-1 for select * . You may not know when table structure is going to change. That can easily break your script. — vasin1987, Apr 04 '14 at 08:03
@vasin1987 - Noted on that. Will change accordingly. Thanks for pointing it out — Kiki, Apr 04 '14 at 08:11

dean · Accepted Answer · 2014-04-04T08:24:45.310

0

You can combine INSERT and DELETE in single statement:

DELETE [DB_Production].[dbo].[tableDetail]
output deleted.col1, deleted.col2, etc into [DB_Archive].[dbo].[tableDetail]
where exists (select * from [DB_Archive].[dbo].[tableMaster] tblMaster where [DB_Production].[dbo].[tableDetail].QuoNo = tblMaster.QuoNo)

edited Apr 04 '14 at 08:24

answered Apr 04 '14 at 07:54

dean

9,960
2
25
26

where tblDet.QuoNo = tblMaster.QuoNo : where should i put tblDet? I try to put it at: into [DB_Archive].[dbo].[tableDetail] tblDet but error to compile – Kiki Apr 04 '14 at 08:20
thanks to you now I know the simple way to do it :) – Kiki Apr 07 '14 at 08:50

Purging Data to history database (Simple and short code)

1 Answers1