资源描述：
《sqlserver迁移数据并删除历史记录》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、SQLSERVER迁移数据并删除历史记录 ?1234567891011121314151617181920212223242526272829303132333435363738394001USE[bak]02GO03/******Object: StoredProcedure[dbo].[pro_pay591_J_orderlog_clear] ScriptDate:08/07/201313:53:49******/04SETANSI_NULLSON05GO06SETQUOTED_IDENTIFIERON07GO08--=
2、============================================09--Author: 10--Createdate:<2013-08-07>11--Description:<清理主数据库订单日志>12--=============================================13ALTERPROCEDURE[dbo].[pro_pay_orderlog_clear]14 @dt_enddatetime--清理此日期之前的数据15AS16BEGIN17 --SETNOCOUN
3、TONaddedtopreventextraresultsetsfrom18 --interferingwithSELECTstatements.19 SETNOCOUNTON;20 414243444546474849505152535455565758596061626364656667686970717273747576777879808182838421 declare@sqlstrvarchar(8000)22declare@table_namevarchar(30)23declare@thedaydatetime
4、24set@theday=dateadd(month,-1,@dt_end)25set@table_name='ORDER_LOG_BACK_'+ltrim(year(@theday-1))+substring(ltrim(month(@theday-1)+100),2,2)26 27set@sqlstr='28 29BEGIN30CREATETABLE[dbo].'+@table_name+'(31 [id][bigint]NOTNULL,32 [order_id][bigint]NULL,33 [op_type][tinyi
5、nt]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]NULLCONSTRAINT[DF__'+@table_name+'__op_ti__4222D4EF] DEFAULT(getdate()),39 [remark][varchar](8000)NULL,40 CONSTRA
6、INT[PK_'+@table_name+']PRIMARYKEYCLUSTERED41(42 [id]Desc85868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712843)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY]44)ON[PRIMARY]45END46'47 48IFNOTEXISTS(SELECT*FROMsys.ob
7、jectsWHEREobject_id=OBJECT_ID(@table_name)ANDtypein(N'U'))49exec(@sqlstr)50 51set@sqlstr='52 53delete frompay.dbo.ORDER_LOG54outputdeleted.*55 into '+@table_name+'([id]56 ,[order_id]57 ,[op_type]58 ,[ip]59 ,[login_name]60
8、 ,[org_data]61 ,[new_data]62 ,[op_time]63 ,[remark])64where[op_time]<'''+convert(varchar(20),@the
