12-19 01:54 阅读 174

关注

SQLServer将表7天前所有数据备份后删除(sql server 定时备份并定期删除)

• –拷贝表后清空数据

• select * into A4JBCSB_BAK from A4JBCSB;

• truncate table A4JBCSB_BAK;

• –插入

• set identity_insert A4JBCSB_BAK ON;

• INSERT INTO A4JBCSB_BAK([TRANSID]

•       ,[A0188]

•       ,[SIGNED]

•       ,[BILL_SUBMITTER]

•       ,[BILL_SUBMIT_DATE]

•       ,[BILL_CODE]

•       ,[CREATOR]

•       ,[CREATIONTIME]

•       ,[MODIFIER]

•       ,[MODIFIEDTIME]

•       ,[FORMTYPE]

•       ,[SP_FORM]

•       ,[A4JBCSB013]

•       ,[A4JBCSB014]

•       ,[A4JBCSB015]

•       ,[A4JBCSB016]

•       ,[A4JBCSB017]

•       ,[A4JBCSB018]

•       ,[A4JBCSB019]) select * from A4JBCSB WHERE DATEDIFF(dd,CREATIONTIME,GETDATE())>=7;

• set identity_insert A4JBCSB_BAK OFF;

• –删除

• delete from A4JBCSB WHERE DATEDIFF(dd,CREATIONTIME,GETDATE())>=7;