asked on

SQL Server deleting old records

Can anyone help.
I have an old file getting larger and larger and I want to delete records older that 7 days old from it. However I have to ling to another table to determine the data of the record.

I also want to insert into a copy ofr the table just in cae I still need to use the data sometime in the future

It goes something like this

declare @cnt int

select @cnt = COUNT(*) from source s (nolock)
inner join EXCEPTION lcdeh (nolock)
on s.source_key = lcdeh.source_key
where s.process_start_date_time < convert(varchar(10),(getdate() - 7),103)

if @cnt > 0 then
I want to insert into an existing copy of the table COPY_EXCEPTION_HEADER
and then delete the entries from the original (EXCEPTION_HEADER)

COPY_EXCEPTION_HEADER already exists and is identical in layout to EXCEPTION_HEADER

END

Ideally I don't want to have to specify all the columns i'm inserting.

any help would be appreciated.

cyberkiwi

begin tran
insert COPY_EXCEPTION_HEADER
select lcdeh.*
from source s (nolock)
inner join EXCEPTION lcdeh (nolock) on s.source_key = lcdeh.source_key
where s.process_start_date_time < convert(varchar(10),(getdate() - 7),103)

delete EXCEPTION
from source s (nolock)
inner join EXCEPTION lcdeh (nolock) on s.source_key = lcdeh.source_key
where s.process_start_date_time < convert(varchar(10),(getdate() - 7),103)
commit

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Harris

ASKER

Thanks for getting back so Quickly.
I'll try that tomorrow as I don't have access to my SQL Server at the moment.

Eric Harris

ASKER

Again apologies for not responding earlier.
Your solution worked brilliant.
Thanks for all your help

Eric Harris

ASKER

Fast and accurate response. THanks