删除历史年度数据只保留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