moving old records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

moving old records

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-17 : 05:50:32

i'm trying to move old records from one table to another so i have a

insert into table form table where date<'dateadd(ww,-6,getdate())
and then
delete from table where date<'dateadd(ww,-6,getdate())

my problem is because i'm minusing 6 weeks and records were added very often by the time the delete is run it's deleting a few more records then were added.
what can i do to prevent this - and how can i put this in a stored procedure that it will only run the delete on the records that were added?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 05:55:22

Create procedure moving_old_records
as
declare @date datetime
set @date=getdate()
insert into table form table where date<dateadd(ww,-6,@date)
delete from table where date<dateadd(ww,-6,@date)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 06:49:16

or use output clause to grab ids and delete

DECLARE @INSERTED_IDS table
(PKCol int)

insert into table
output inserted.PKCol INTO @INSERTED_IDS 
select fields from table where date<'dateadd(ww,-6,getdate())

delete t from table t
inner join @INSERTED_IDS i 
on t.PKCol=i.PKCol

PKCol is the key column of table from which records are inserted

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-17 : 06:58:20

is there anything wrong with doing it madhivanan way?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-17 : 07:05:50

quote:
Originally posted by esthera

is there anything wrong with doing it madhivanan way?

No. He just showed alternate method

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources