批量录入

来源：互联网发布：淘宝摄影师的要求编辑：程序博客网时间：2024/04/29 06:03

--select * from temp_leavemanagement
--select * from s_org where
--select * from s_user where

INSERT INTO ycoms_leavemanagement
(
LEAVEMANAGEMENTID,
ACTORNO,
ORGANNO,
ACTORNAME,
STAFFSEX,
CATEGORY,
LEAVEDAYS,
SURPLUSDAYS,
FJFLOG,
VIRTUALDAYS,
VIRTUALAPPDAYS )
with lminfo as
(select tl.*, su.orgid orgid,su.staffsex staffsex
    from temp_leavemanagement tl
    left join s_user su
      on tl.actorno = su.actorno
)
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'01',nvl(t.leavedays,0),nvl(t.surplusdays,0),'01',nvl(t.surplusdays,0),nvl(t.leavedays,0) from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'02',0,nvl(t.sljdays,0),'01',nvl(t.sljdays,0),0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'03',0,0,'02',0,0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'04',0,0,'01',0,0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'05',0,0,'02',0,0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'06',0,0,'01',0,0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'07',0,0,'01',0,0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'08',0,0,'02',0,0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'09',0,0,'01',0,0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'10',0,0,'02',0,0 from (select * from lminfo ) t union all
SELECT sys_guid(),t.actorno,t.orgid,t.actorname,t.staffsex,'11',0,0,'02',0,0 from (select * from lminfo ) t
-- select * from temp_leavemanagement
--select * from s_dic where memo like '%附件%'
--delete ycoms_leavemanagement
select * from ycoms_leavemanagement s order by s.actorno ,s.category