Query to move some records and then delete them

Question

I do apologize if my question sounds too easy for some of you. I'm new to SQL Server and I'm desperately in need of an advise. I did my research, but I'm still unsure how to proceed as all articles I read about it seem a bit too generic.

My task is to keep table ABC (on server 1) free of records older than 3 months. Records, which are older than 3 months should be automatically moved to the table ABC_ARCHIVE on server 2. Just coping and deleting records is not a big deal, but I'd like to automatically check that the records are already on Server 2 before deleting them from the server 1.

I thought about using a query, which I will run in SSIS package, which in turn will be run in a SQL Server Agent job. If you think that I should go differently about it, please let me know. I prefer to avoid involving any unneeded components.

To select records older than 3 months I need something like the query below:

Select *
From ABC
WHERE [CreateDate] <= DATEADD(mm, -3, GETDATE())

then the same one with "delete" in place of "select" to delete them.

What do I write in between these two queries to make sure that the rows were indeed transferred before deleting them? How to automatically check that the records are already on Server 2 before deleting them from the server 1? If its impossible to do it this way, could you please suggest alternative solution?

I'm using Enterprise x64 edition and my accounts has sysadmin privileges. I am very comfortable with creating jobs in SQL Agent and moderately comfortable with SSIS(tips will be very welcome).

I'll be very grateful for any tips and suggestions. Thank you in advance!

Answer 1

You use the OUTPUT clause for operations like this:

Here is a working example using table variables:

DECLARE @t1 table(CreateDate datetime, col1 int, col2 int)
DECLARE @t2 table(CreateDate datetime, col1 int, col2 int)
INSERT @t1 values('2015-01-01', 1,1)
INSERT @t1 values('2016-01-01', 1,1)

DELETE
FROM @t1
OUTPUT deleted.CreateDate, deleted.col1, deleted.col2 into @t2
WHERE [CreateDate] <= DATEADD(mm, -3, GETDATE())

SELECT * FROM @t1

SELECT * FROM @t2

Answer 2

1. Create a table variable or temp table to store a list of IDs of the records you are moving.
2. Do an INSERT statement to copy the records from one database to another.
3. DELETE records from the original database based on IDs which are in your temp table joined to the new database.

Using a temp table like this prevents you from capturing different records moment to moment as getdate() value changes constantly.