sql将smslog拆分3天3月内，3月外表

--**************** 存储过程 sql *******************
Create procedure Proc_MoveSms_ToHistory
as
begin
SET XACT_ABORT on
BEGIN TRAN
--批量插入到历史表
INSERT INTO dbo.SMS_T_SendLog
SELECT a.ID, b.Host,b.Guest,a.SendedTime,b.State,GETDATE() AS AddTime,
a.AgentID,a.Title,a.MsgContent,a.IsFast,a.SendType,a.PreSendTime,b.WhichBrand, CONVERT(varchar(12) , ISNULL(a.SendedTime,a.AddTime), 112 )
FROM dbo.SMS_T_SendBatchList a ,dbo.SMS_T_SendScan b
WHERE a.ID &＃61; b.BatchID AND a.IsDone&＃61;1
--删除扫描表里的记录
DELETE SMS_T_SendScan WHERE BatchID IN
(SELECT ID FROM SMS_T_SendBatchList WHERE IsDone&＃61;1 )
COMMIT TRAN

end

Create procedure Proc_SplitSmsHistory
--将smslog拆分3天3月内&＃xff0c;3月外表
as
begin
SET XACT_ABORT on
BEGIN TRAN
--移动3天到3个月的记录到SmsLog2
INSERT INTO dbo.SMS_T_SendLog2 SELECT * FROM SMS_T_SendLog WHERE AddTime < DATEadd(day,-3,CONVERT(varchar(30), GETDATE(), 23))
--移动3个月外 的记录到SmsLog2
INSERT INTO dbo.SMS_T_SendLog3 SELECT * FROM SMS_T_SendLog2 WHERE AddTime < DATEadd(month,-3,CONVERT(varchar(30), GETDATE(), 23))
DELETE SMS_T_SendLog WHERE AddTime < DATEadd(day,-3,CONVERT(varchar(30), GETDATE(), 23))
DELETE SMS_T_SendLog2 WHERE AddTime < DATEadd(month,-3,CONVERT(varchar(30), GETDATE(), 23))
COMMIT TRAN

end ;