SQLServer将表7天前所有数据备份后删除

wwh • 2020-09-28 11:10 • 服务器维护 • 阅读 2590

–拷贝表后清空数据
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;