金蝶KIS&K3助记码SQL数据库批量刷新

金蝶KIS&K3助记码SQL数据库批量刷新

用的次数不多，就没有写入存储过程或者触发里面了，可以自行实现。

第一步选择对应账套的数据库，执行下面的命令，这个是一个函数。

go 
if exists (select * from sysobjects where name='fun_getPY' and xtype='Fn') 
begin 
drop function fun_getPY 
end 
go 
create function fun_getPY(@str nvarchar(4000)) 
returns nvarchar(4000) 
as 
begin 
declare @word nchar(1),@PY nvarchar(4000) 
set @PY='' 
while len(@str)>0 
begin 
set @word=left(@str,1) 
--如果非汉字字符，返回原字符 
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 
then (select top 1 PY from ( 
select 'A' as PY,N'驁' as word 
union all select 'B',N'簿' 
union all select 'C',N'錯' 
union all select 'D',N'鵽' 
union all select 'E',N'樲' 
union all select 'F',N'鰒' 
union all select 'G',N'腂' 
union all select 'H',N'夻' 
union all select 'J',N'攈' 
union all select 'K',N'穒' 
union all select 'L',N'鱳' 
union all select 'M',N'旀' 
union all select 'N',N'桛' 
union all select 'O',N'漚' 
union all select 'P',N'曝' 
union all select 'Q',N'囕' 
union all select 'R',N'鶸' 
union all select 'S',N'蜶' 
union all select 'T',N'籜' 
union all select 'W',N'鶩' 
union all select 'X',N'鑂' 
union all select 'Y',N'韻' 
union all select 'Z',N'咗' 
) T 
where word>=@word collate Chinese_PRC_CS_AS_KS_WS 
order by PY ASC) else @word end) 
set @str=right(@str,len(@str)-1) 
end 
return @PY 
end 
go 

--物料
update     t_ICItemCore set FHelpcode=dbo.fun_getPY(left(Fname,30)) 
go

 

第二步，更新你需要的地方，物料、部门、职员、仓库、供应商、客户等的助记码

有些表格的字段需要自己做对应，下面的更新不能应用于所有版本，如执行错误，请自行调试，谢谢

--物料
update     t_ICItemCore set FHelpcode=dbo.fun_getPY(left(Fname,30)) 
go


--部门
update     t_Department set FHelpcode=dbo.fun_getPY(left(Fname,30)) 
go


--职员
update      t_Emp  set FHelpcode=dbo.fun_getPY(left(Fname,30)) 
go


--仓库
update      t_Stock  set FHelpcode=dbo.fun_getPY(left(Fname,30)) 
go

--供应商
update       t_Supplier  set FHelpcode=dbo.fun_getPY(left(Fname,30)) 
go

    

--客户
update       t_Organization set FHelpcode=dbo.fun_getPY(left(Fname,30)) 
go