記帳資料隻記部分後如何處理

查詢記賬中間表中是否有資料

select *  from INT_WMS_XSCK_BILL where djbh not in(select djbh from jzorder_hz)

如果有将其單據編号插入表#a

檢查明細資料并插入兩張表

insert into jzorder_mx(djbh, dj_sn,  dj_sort, spid, dw, jlgg, pihao, baozhiqi, sxrq, baozhshl, lingsshl, hw, shl, dj, hshj, koul, je, shlv, she, hsje, lshj, zengp, wlqrshl, wlqxsl, wlqrjg, wlqrje, wlqrshe, wlqrhsje, is_wlzx, kfqrshl, kfqxsl, is_kfzx, is_zx, is_gsp, isjs, istk, yiwchsl, canssl, quxsl, yiwchje, yiwchsje, pici, fapiaoh, bukccb, nhshj, yhshj, jiansh, buckcb, is_hs, chbdj, yhsshl, yhesje, xgdjbh, recnum, ys_sn, ydj, ndj, weixsl, yixsl, duiydjbh, duiydj_cn, lshje, phyqlx, miejph, beizhu, is_jush, koulv, koulv1, koulv2, koulv3, dj2, je2, koulv4, thyy, zy_dy, qdchbdj, zdshj, jwh, dwbhh, jsshl, pckhdj, hsjj, ml2, hwshl, chbdj2, xsbmxz, jywdd, lhfukfs, pcxsxj, isdstj, mjpihao)

select distinct a.djbh, dj_sn,   dj_sort, a.spid, dw, jlgg, a.pihao, a.baozhiqi, a.sxrq, baozhshl, lingsshl, a.hw, a.shl, dj, a.hshj, koul, je, shlv, she, hsje, lshj, zengp, 0, 0, 0, 0, 0, 0, '否', 0, 0, '否', is_zx, '否', '否', '否', yiwchsl, 0, quxsl, 0, 0, a.pici, fapiaoh, 0, nhshj, yhshj, jiansh, 0, '否', chbdj, 0, 0, xgdjbh, recnum, dj_sn, ydj, ndj, 0, 0, duiydjbh, duiydj_cn, lshje, '', a.miejph, beizhu, '', NULL, NULL, NULL, NULL, NULL, NULL, koulv4, thyy, zy_dy, qdchbdj, 0, a.jwh, dwbhh, 0, a.pckhdj, hsjj, ml2, hwshl, chbdj2, xsbmxz, jywdd, c.fukfs, a.pckhdj, isdstj, a.mjpihao 

from pf_djmx a,sphwph b,(select djbh,fukfs from pf_djhz) c 

where a.djbh=c.djbh and a.djbh in(select * from #a) and a.spid=b.spid and a.pici=b.pici

insert into jzorder_hz(djbh, djbs, djlx, bendian, rq, is_wlqr, is_kfqr, dwbh, ywy, bm, is_zx, shenhe, shenhr, shenhrq, shenhyj, yanshr, yansrq, jsfsid, sjsz, isjs, tkjsfs, tkje, xg, shenchr, piaohao, ysyfje, bezzs, use_ysyf, beizhu, username, kaiprq, ontime, ywbmid, ywyid, XTYPE, fukfs, fukqx, ydhrq, jingd, thfs, bezzs_, shenhe_, beizhu4, dfywy, fukuanfs, caig_shenhyj, caig_shenhr, caig_shenhe, caig_shenhrq, shenhe_cw, shenher_cw, shenherq_cw, shenheyj_cw, ddsj, cw_shenh, cw_shenhrq, cw_shenhyj, cw_shenhr, lxr, ywlxr, xszxshx, bmgs, rktype)

select djbh, djbs, djlx, bendian, rq, '否', '否', a.dwbh, a.ywy, bm, is_zx, shenhe, shenhr, a.shenhrq, shenhyj,

 '', '', '', 0, '否', '', 0, '', '', piaohao, 0, bezzs, '否', a.beizhu, username, kaiprq, 

ontime, ywbmid, ywyid, '', fukfs, 'NULL', 'NULL', '經銷', thfs, bezzs_, shenhe_, beizhu4, '', fukuanfs, 

'', '', '', '', shenhe_cw, shenher_cw, shenherq_cw, shenheyj_cw, 

'', '', '', '', '', '', b.ywy , '', '', rktype from pf_djhz a,mchk b where djbh in(select * from #a) and a.dwbh=b.dwbh 

删除曆史表hz資料

delete jzorder_hz_his where djbh in (select djbh from #a)

本文轉自 qvodnet 51CTO部落格，原文連結:http://blog.51cto.com/bks2015/1933370