Link to home
Start Free TrialLog in
Avatar of Faith Victory
Faith VictoryFlag for United States of America

asked on

Where Clause For Archive Table

I have 5 yrs records in the production table consisting of 2013 records, 2014 records, 2015 record, 2016 records,
and 2017 records and I need to move the past 4yrs records to the Archive table and also delete from the production table
after moving them to the Archive table, leaving 1 year records in the production table.

My question:
How do I achieve this in the Where Clause by moving these records nightly in batches of 1 yrs each night.
Can you give me the script.
 

e.g
insert into my table (col, col2,....)
select into my table (col, col2,....)
FROM [dbo].[CandidateHist]
WHERE (CandidateTS < dateadd(wk,-12,current_TimeStamp))


Delete FROM [dbo].[CandidateHist]
WHERE (CandidateTS < dateadd(wk,-12,current_TimeStamp))
Avatar of Qlemo
Qlemo
Flag of Germany image

First of all, you need to use a datetime variable to keep the point in time used for both commands in sync, otherwise you'll delete more than what you have archived (as time progresses).
I would also make sure to cut off the time portion of that date.
begin tran
declare @date date = dateadd(wk, -12, getdate())

insert into mytable (col, col2,....)
FROM [dbo].[CandidateHist]
WHERE CandidateTS < @date

Delete FROM [dbo].[CandidateHist]
WHERE CandidateTS < @date
commit

Open in new window

I would do something like this that is slightly different, but gets the same results.

Select * into ArchiveTbl 
FROM [dbo].[CandidateHist]
Where  Datediff(day, CandidateTS, getdate()) > 365;

delete  [dbo].[CandidateHist]
Where  Datediff(day, CandidateTS, getdate()) > 365;

Open in new window

Bad idea, because it uses a function on a database column in the where clause. If you have a constant date to check against, it is best to put any calculation into that date constant ;-).
Thanks for that info. I assume that on a small set of data you will not see the performance hit, but if the data set is large that is when you can start seeing this.
What if you used day ver. weeks.
The issue is that the DBMS cannot use an index as soon as you use dateadd, datediff etc.. Using datediff hence will most likely result in a full table scan.
Avatar of Faith Victory

ASKER

You guys are awesome! The quick responses are impressive.


Please can one of you give or point  me to the best script without huge performance impact.
MIne is the best, of course!
Besides the function use issue, you can apply SELECT INTO only if the target table does not exist yet. You certainly don't want to create a new archive table each day (or night).
I would also run both and see what works for you.

Both are methods that get you the desired results.

I find my method to insert into a works with the least amount of coding because you do not have to build the insert into (col1,col2,col3, .....).
My where clause might not be the best,but it is an option.

Maybe build a script that builds both suggestions into a single statement.
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Qlemo last comment,

How I can do this in a live production table with applications referencing it for reads?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Everyone was very helpful