原代码如下:
ALTER proc cz_hz.sp_split_all
asdeclare @DbNm varchar(20)declare @jgdh char(4)
declare @dbm varchar(20)
declare @userm varchar(20)
declare @bm varchar(20)
declare @jgzd varchar(20)
declare @sql varchar(4000)
declare @sql1 varchar(4000)set @DbNm='cz_hz.cz_hz'declare c_jg cursor for select zhngjg,dbname,username,tb_name,zhngjg_zd
from (
--如abwfh,可能有机构1000、1001,分机构两次插入,pywdh没有机构一次插入
select p.zhngjg as zhngjg,p.dbname as dbname,p.username as username,b.tb_name as tb_name,b.zhngjg as zhngjg_zd
from
(select a.zhngjg as zhngjg,a.dbname as dbname,c.username as username
from
(select yngyjg as zhngjg,case when gnlisj='9900'
then 'cz_'+yngyjg
else 'cz_'+gnlisj end as dbname
--处理,如1001,拆分到cz_1000中
from cz_hz.pjgcs
where yngyjg not like '99%' ) a
left join hps_report_cz_org c
on a.dbname=c.dbname) p ,
(select * from hps_report_cz_tabinfo where len(rtrim(ltrim(zhngjg)))<>0 and zhngjg is not null and updateflag2<>'0') b
union
select p2.zhngjg as zhngjg,p2.dbname as dbname,p2.username as username,b2.tb_name as tb_name,b2.zhngjg as zhngjg_zd
from
(select d.zhngjg as zhngjg,d.dbname as dbname,d.username as username
from hps_report_cz_org d) p2 ,
(select * from hps_report_cz_tabinfo where (len(rtrim(ltrim(zhngjg)))=0 or zhngjg is null) and updateflag2<>'0') b2
) bbopen c_jgfetch next from c_jg into @jgdh,@dbm,@userm,@bm,@jgzd
while @@fetch_status =0
begin
--插入数据
if @jgzd is null or ltrim(rtrim(@jgzd))=''
set @sql='insert into '+@dbm+'.'+@userm+'.'+@bm+' select * from '+@DbNm+'.'+@bm
else
set @sql='insert into '+@dbm+'.'+@userm+'.'+@bm+' select * from '+@DbNm+'.'+@bm+' where '+@jgzd+'='''+@jgdh+''''
--select @sql
exec (@sql)
fetch next from c_jg into @jgdh,@dbm,@userm,@bm,@jgzd
endclose c_jg
deallocate c_jg此代码可以实现将cz_hz.cz_hz拆分为cz_1000.cz_1000,cz_1100.cz_1100,cz_1200.cz_1200等等分库,但效率极其低下,求解用其他更有效率的SQL方式实现相同功能,我将全部分送上,分不够时换大号追加!
ALTER proc cz_hz.sp_split_all
asdeclare @DbNm varchar(20)declare @jgdh char(4)
declare @dbm varchar(20)
declare @userm varchar(20)
declare @bm varchar(20)
declare @jgzd varchar(20)
declare @sql varchar(4000)
declare @sql1 varchar(4000)set @DbNm='cz_hz.cz_hz'declare c_jg cursor for select zhngjg,dbname,username,tb_name,zhngjg_zd
from (
--如abwfh,可能有机构1000、1001,分机构两次插入,pywdh没有机构一次插入
select p.zhngjg as zhngjg,p.dbname as dbname,p.username as username,b.tb_name as tb_name,b.zhngjg as zhngjg_zd
from
(select a.zhngjg as zhngjg,a.dbname as dbname,c.username as username
from
(select yngyjg as zhngjg,case when gnlisj='9900'
then 'cz_'+yngyjg
else 'cz_'+gnlisj end as dbname
--处理,如1001,拆分到cz_1000中
from cz_hz.pjgcs
where yngyjg not like '99%' ) a
left join hps_report_cz_org c
on a.dbname=c.dbname) p ,
(select * from hps_report_cz_tabinfo where len(rtrim(ltrim(zhngjg)))<>0 and zhngjg is not null and updateflag2<>'0') b
union
select p2.zhngjg as zhngjg,p2.dbname as dbname,p2.username as username,b2.tb_name as tb_name,b2.zhngjg as zhngjg_zd
from
(select d.zhngjg as zhngjg,d.dbname as dbname,d.username as username
from hps_report_cz_org d) p2 ,
(select * from hps_report_cz_tabinfo where (len(rtrim(ltrim(zhngjg)))=0 or zhngjg is null) and updateflag2<>'0') b2
) bbopen c_jgfetch next from c_jg into @jgdh,@dbm,@userm,@bm,@jgzd
while @@fetch_status =0
begin
--插入数据
if @jgzd is null or ltrim(rtrim(@jgzd))=''
set @sql='insert into '+@dbm+'.'+@userm+'.'+@bm+' select * from '+@DbNm+'.'+@bm
else
set @sql='insert into '+@dbm+'.'+@userm+'.'+@bm+' select * from '+@DbNm+'.'+@bm+' where '+@jgzd+'='''+@jgdh+''''
--select @sql
exec (@sql)
fetch next from c_jg into @jgdh,@dbm,@userm,@bm,@jgzd
endclose c_jg
deallocate c_jg此代码可以实现将cz_hz.cz_hz拆分为cz_1000.cz_1000,cz_1100.cz_1100,cz_1200.cz_1200等等分库,但效率极其低下,求解用其他更有效率的SQL方式实现相同功能,我将全部分送上,分不够时换大号追加!
cz_1000,cz_1100,cz_1200这些是按库名拆分,下面还要按机构拆分,如cz_1000.cz_1000,cz_1100.cz_1100,cz_1200.cz_1200等等,而且分库的数量是不确定的(以后会增加新的分库以及新的机构),目前只想到用这游标实现,但效率极其低下~头痛ing```