Faith Victory
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_Tim eStamp))
Delete FROM [dbo].[CandidateHist]
WHERE (CandidateTS < dateadd(wk,-12,current_Tim eStamp))
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_Tim
Delete FROM [dbo].[CandidateHist]
WHERE (CandidateTS < dateadd(wk,-12,current_Tim
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;
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.
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.
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).
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Qlemo last comment,
How I can do this in a live production table with applications referencing it for reads?
How I can do this in a live production table with applications referencing it for reads?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Everyone was very helpful
I would also make sure to cut off the time portion of that date.
Open in new window