SQL SERVER迁移数据并删除历史记录

01
USE [bak]
02
GO
03
/****** Object:  StoredProcedure [dbo].[pro_pay591_J_orderlog_clear]    Script Date: 08/07/2013 13:53:49 ******/
04
SET ANSI_NULLS ON
05
GO
06
SET QUOTED_IDENTIFIER ON
07
GO
08
-- =============================================
09
-- Author:  
10
-- Create date: <2013-08-07>
11
-- Description: <清理主数据库订单日志>
12
-- =============================================
13
ALTER PROCEDURE [dbo].[pro_pay_orderlog_clear]
14
 @dt_end datetime --清理此日期之前的数据
15
AS
16
BEGIN
17
 -- SET NOCOUNT ON added to prevent extra result sets from
18
 -- interfering with SELECT statements.
19
 SET NOCOUNT ON;
20

21
   declare @sqlstr varchar(8000)
22
declare @table_name varchar(30)
23
declare @theday datetime
24
set @theday = dateadd(month,-1,@dt_end)
25
set @table_name = 'ORDER_LOG_BACK_'+ltrim(year(@theday-1))+substring(ltrim(month(@theday-1)+100),2,2)
26

27
set @sqlstr = '
28

29
BEGIN
30
CREATE TABLE [dbo].'+@table_name+'(
31
 [id] [bigint] NOT NULL,
32
 [order_id] [bigint] NULL,
33
 [op_type] [tinyint] NULL,
34
 [ip] [varchar](50) NULL,
35
 [login_name] [varchar](50) NULL,
36
 [org_data] [varchar](1024) NULL,
37
 [new_data] [varchar](1024) NULL,
38
 [op_time] [datetime] NULL CONSTRAINT [DF__'+@table_name+'__op_ti__4222D4EF]  DEFAULT (getdate()),
39
 [remark] [varchar](8000) NULL,
40
 CONSTRAINT [PK_'+@table_name+'] PRIMARY KEY CLUSTERED
41
(
42
 [id] Desc
43
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
44
) ON [PRIMARY]
45
END
46
'
47

48
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table_name) AND type in (N'U'))
49
exec(@sqlstr)
50

51
set @sqlstr = '
52

53
delete  from pay.dbo.ORDER_LOG
54
output deleted.*
55
 into  '+@table_name+' ([id]
56
           ,[order_id]
57
           ,[op_type]
58
           ,[ip]
59
           ,[login_name]
60
           ,[org_data]
61
           ,[new_data]
62
           ,[op_time]
63
           ,[remark])
64
where [op_time] < '''+convert(varchar(20),@theday)+''''
65
exec(@sqlstr)
66
END