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.
Eric HarrisDeveloperAsked:
Who is Participating?
 
cyberkiwiCommented:
I notice that your SQL is not entirely correct (it contains EXCEPTION table instead of _HEADER) but you can see where I'm going.

If your COPY_EXCEPTION_HEADER has an identity column, then change the insert part to

set identity_insert COPY_EXCEPTION_HEADER on;
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);
set identity_insert COPY_EXCEPTION_HEADER off;
0
 
cyberkiwiCommented:
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
0
 
Eric HarrisDeveloperAuthor Commented:
Thanks for getting back so Quickly.
I'll try that tomorrow as I don't have access to my SQL Server at the moment.
0
 
Eric HarrisDeveloperAuthor Commented:
Again apologies for not responding earlier.
Your solution worked brilliant.
Thanks for all your help
0
 
Eric HarrisDeveloperAuthor Commented:
Fast and accurate response. THanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.