删除历史年度数据只保留091011三个年度Word文档下载推荐.docx

删除历史年度数据只保留091011三个年度Word文档下载推荐.docx

《删除历史年度数据只保留091011三个年度Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《删除历史年度数据只保留091011三个年度Word文档下载推荐.docx（8页珍藏版）》请在冰豆网上搜索。

13

ng0001bf数据库

select*intong0001bf.dbo.ordermstfromng0001.dbo.ordermst

（所影响的行数为42467行）

selectcount（*）fromng0001.dbo.ordermst

select*fromordermst

selectsubstring（sysno,1,3）,count（*）fromng0001.dbo.ordermstgroupbysubstring（sysno,1,3）

P05381

P0614833

P0718211

P089042

准备将05年及06年数据删除。

-----------------------------

1ordermstorderdet

selectsubstring（sysno,1,3）,count（*）fromorderdetgroupbysubstring（sysno,1,3）

select*fromordermst

wheresysnolike'

P05%'

orsysnolike'

P06%'

orsysnolike'

P07%'

P08%'

deletefromordermst

deletefromorderdet

selectsysnofromsendmst

Q05%'

Q06%'

Q07%'

Q08%'

orderbysysno

deletefromsendmst

deletefromsenddet

selectsysnofromsbackdet

S05%'

S06%'

S07%'

S08%'

ORDERBYSYSNO

DELETEfromsbackdet

DELETEfromsbackmst

deletefromsbillmst

deletefromsbilldet

selectsysnofromsbillmst

selectspurnofrompurmst

wherespurnolike'

G05%'

orspurnolike'

G06%'

orspurnolike'

G07%'

G08%'

deletefrompurmst

deletefrompurdec

selectsrtnnofromrtnmst

wheresrtnnolike'

J05%'

orsrtnnolike'

J06%'

orsrtnnolike'

J07%'

J08%'

deletefromrtnmst

deletefromrtndec

selectsrecnofromrecmst

wheresrecnolike'

205%'

orsrecnolike'

206%'

orsrecnolike'

207%'

208%'

deletefromrecmst

deletefromrecdet

selectsrcvnofromrcvmst

wheresrcvnolike'

K05%'

orsrcvnolike'

K06%'

orsrcvnolike'

K07%'

K08%'

DELETEfromrcvmst

DELETEfromrcvdet

selectordnofrommps_mfg_ord

whereordnolike'

N05%'

orordnolike'

N06%'

orordnolike'

N07%'

N08%'

ORDERBYordno

deletefrommps_mfg_ord

selectordnofrommps_mfg_det

deletefrommps_mfg_det

selectordnofrommps_allocation

deletefrommps_allocation

selectreq_nofrommrp_req_det

wherereq_nolike'

R05%'

orreq_nolike'

R06%'

orreq_nolike'

R07%'

R08%'

deletefrommrp_req_det

selectsysnofrommdm_proc_reqdtl

U05%'

U06%'

U07%'

U08%'

deletefrommdm_proc_reqdtl

deletefrommdm_proc_reqmst

selectbmordnofrommps_back_makeup

wherebmordnolike'

2005%'

orbmordnolike'

2006%'

orbmordnolike'

2007%'

2008%'

deletefrommps_back_makeup

billbodybillhead

selecttransidfrombillhead

wheretransidlike'

05%'

ortransidlike'

06%'

ortransidlike'

07%'

08%'

deletefrombillhead

deletefrombillbody

清除日志

DUMPTRANSACTIONNG0003WITHNO_LOG

再收缩日志文件大小

invtory

selectuyearfrominvtory

whereuyear='

2007'

oruyear='

2008'

deletefrominvtory