SQL归档

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

/* 作用：归档3个月前的短信发送记录 创建人：zhuxiang 日期：2012-10-30 */ ALTER Proc [dbo].[Sms_Job_History_Send] As Begin     Declare @Temp table (MsgID varchar(20))     Declare @Time datetime Set @Time = DateAdd(mm,-3,getdate())           --检索出需要归档的记录     Insert Into @Temp(MsgID)     Select top 5000 MsgID From IB_Sms_Send_Bill(nolock) Where SubmitTime < @Time and State = 9           --删除主表     Delete a     Output deleted.*     Into ytFax_DB_History..IB_Sms_Send_Bill     From IB_Sms_Send_Bill a,@Temp b     Where a.MsgID = b.MsgID           --删除明细表     Delete a     Output deleted.*     Into ytFax_DB_History..IB_Sms_Send_Detail_Bill     From IB_Sms_Send_Detail_Bill a,@Temp b     Where a.MsgID = b.MsgID End