业务需求如下： 某公司目前用了用友的总账bi分析案例。 /* sql server2012使用作业设置定时任务,来保证一天执行一次 */ /* 一定要注意temp1表里一定要保证要有记录，否则以temp1 来 left join就出现为空的情况。 */ /* step 1: 把所有的数据库列表都插入到bi
业务需求如下：
某公司目前用了用友的总账bi分析案例。
/*
sql server2012使用作业设置定时任务,来保证一天执行一次
*/
/*
一定要注意temp1表里一定要保证要有记录，否则以temp1 来 left join就出现为空的情况。
*/
/*
step 1: 把所有的数据库列表都插入到bi数据库的dblist表里
*/
use bi;
/*drop table bi.dbo.temp1;drop table bi.dbo.temp2;drop table bi.dbo.temp3;drop table bi.dbo.temp4;drop table bi.dbo.temp5;drop table bi.dbo.temp6;drop table bi.dbo.temp7;*/truncate table bi.dbo.dblist;select * from bi.dbo.dblist;insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_103_2010','公司名称1');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_103_2011','公司名称1');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_103_2012','公司名称1');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_103_2013','公司名称1');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_103_2014','公司名称1');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_105_2010','公司名称2‘);insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_105_2011','公司名称2);insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_105_2012','公司名称2');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_105_2013','公司名称2');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_105_2014','公司名称2');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_106_2010','公司名称3’);insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_106_2011','公司名称3');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_106_2012','公司名称3');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_106_2013','公司名称3');insert into bi.dbo.dblist(db_code,db_company_name) values ('ufdata_106_2014','公司名称3');/*step 2: 清空统计表里的记录，方便重新插入，注意设置bi数据库不记录日志的形式*/truncate table bi.dbo.clb;truncate table bi.dbo.cwb;truncate table bi.dbo.rcshb;truncate table bi.dbo.rsb;truncate table bi.dbo.tdjs;truncate table bi.dbo.zjb;/*step 3: 循环选择数据库，插入bi.dbo.clb*/declare @db_code char(50)declare @db_company_name char(100)declare @sql_string varchar(8000)declare table_cursor cursor for select db_code,db_company_name from bi.dbo.dblistopen table_cursorfetch next from table_cursor into @db_code,@db_company_namewhile @@fetch_status = 0begin/*差旅表 clb*/set @sql_string='select ' +''''+ '差旅表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'domestictravel'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp1 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660214'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '差旅表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'overseastravel'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp2 from '+ rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660213'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '差旅表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'entertainment'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp3 from '+ rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660223'+''''+';'set @sql_string=@sql_string+' insert into bi.dbo.clb(gltype,companyname,domestictravel,overseastravel,entertainment,iyperiod,iperiod,iyear)'set @sql_string=@sql_string+' select bi.dbo.temp1.gltype,bi.dbo.temp1.companyname,'set @sql_string=@sql_string+' bi.dbo.temp1.domestictravel,'set @sql_string=@sql_string+' bi.dbo.temp2.overseastravel,'set @sql_string=@sql_string+' bi.dbo.temp3.entertainment,'set @sql_string=@sql_string+' bi.dbo.temp1.iyperiod,'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod,bi.dbo.temp1.iyear from bi.dbo.temp1 left join bi.dbo.temp2 on'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp2.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp2.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp2.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp3 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp3.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp3.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp3.iyperiod;'set @sql_string=@sql_string+' drop table bi.dbo.temp1;'set @sql_string=@sql_string+' drop table bi.dbo.temp2;'set @sql_string=@sql_string+' drop table bi.dbo.temp3;'exec(@sql_string)print @sql_string/*财务表 cwb*/set @sql_string='select ' +''''+ '财务表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'bankcharges'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp1 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660301'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '财务表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'interestincome'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp2 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660302'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '财务表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'interestexpenses'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp3 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660303'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '财务表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'incometaxes'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp4 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'6801'+''''+';'set @sql_string=@sql_string+' insert into bi.dbo.cwb(gltype,companyname,bankcharges,interestincome,interestexpenses,incometaxes,iyperiod,iperiod,iyear)'set @sql_string=@sql_string+' select bi.dbo.temp1.gltype,bi.dbo.temp1.companyname,'set @sql_string=@sql_string+' bi.dbo.temp1.bankcharges,'set @sql_string=@sql_string+' bi.dbo.temp2.interestincome,'set @sql_string=@sql_string+' bi.dbo.temp3.interestexpenses,'set @sql_string=@sql_string+' bi.dbo.temp4.incometaxes,'set @sql_string=@sql_string+' bi.dbo.temp1.iyperiod,'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod,bi.dbo.temp1.iyear from bi.dbo.temp1 left join bi.dbo.temp2 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp2.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp2.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp2.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp3 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp3.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp3.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp3.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp4 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp4.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp4.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp4.iyperiod;'set @sql_string=@sql_string+'drop table bi.dbo.temp1;'set @sql_string=@sql_string+'drop table bi.dbo.temp2;'set @sql_string=@sql_string+'drop table bi.dbo.temp3;'set @sql_string=@sql_string+'drop table bi.dbo.temp4;'exec(@sql_string)print @sql_string/*日常生活表 rcshb*/set @sql_string='select ' +''''+ '日常生活表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'fixlinetel'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp1 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660211'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '日常生活表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'mobilephone'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp2 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660212'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '日常生活表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'officesupplies'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp3 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660221'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '日常生活表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'officerepairmaintenance'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp4 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660222'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '日常生活表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'waterelectricity'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp5 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660231'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '日常生活表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'misexpense'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp6 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660233'+''''+';'set @sql_string=@sql_string+' insert into bi.dbo.rcshb(gltype,companyname,fixlinetel,mobilephone,officesupplies,officerepairmaintenance,waterelectricity,misexpense,iyperiod,iperiod,iyear)'set @sql_string=@sql_string+' select bi.dbo.temp1.gltype,bi.dbo.temp1.companyname,'set @sql_string=@sql_string+' bi.dbo.temp1.fixlinetel,'set @sql_string=@sql_string+' bi.dbo.temp2.mobilephone,'set @sql_string=@sql_string+' bi.dbo.temp3.officesupplies,'set @sql_string=@sql_string+' bi.dbo.temp4.officerepairmaintenance,'set @sql_string=@sql_string+' bi.dbo.temp5.waterelectricity,'set @sql_string=@sql_string+' bi.dbo.temp6.misexpense,'set @sql_string=@sql_string+' bi.dbo.temp1.iyperiod,'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod,bi.dbo.temp1.iyear from bi.dbo.temp1 left join bi.dbo.temp2 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp2.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp2.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp2.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp3 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp3.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp3.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp3.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp4 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp4.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp4.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp4.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp5 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp5.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp5.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp5.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp6 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp6.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp6.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp6.iyperiod;'set @sql_string=@sql_string+' drop table bi.dbo.temp1;'set @sql_string=@sql_string+' drop table bi.dbo.temp2;'set @sql_string=@sql_string+' drop table bi.dbo.temp3;'set @sql_string=@sql_string+' drop table bi.dbo.temp4;'set @sql_string=@sql_string+' drop table bi.dbo.temp5;'set @sql_string=@sql_string+' drop table bi.dbo.temp6;'exec(@sql_string)print @sql_string/*人事表 rsb*/set @sql_string='select ' +''''+ '人事表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'salary'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp1 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'66020101'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '人事表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'bonus'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp2 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'66020102'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '人事表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'salescommission'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp3 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'66020103'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '人事表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'socialinsurance'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp4 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660203'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '人事表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'benefits'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp5 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660204'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '人事表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'housingotherallowance'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp6 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660205'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '人事表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'0 as '+''''+'headcount'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp7 from '+rtrim(@db_code)+'.dbo.gl_accsum where 1=2;'set @sql_string=@sql_string+' insert into bi.dbo.rsb(gltype,companyname ,salary ,bonus ,salescommission,socialinsurance,benefits,housingotherallowance,headcount,iyperiod,iperiod,iyear)'set @sql_string=@sql_string+' select bi.dbo.temp1.gltype,bi.dbo.temp1.companyname,'set @sql_string=@sql_string+' bi.dbo.temp1.salary,'set @sql_string=@sql_string+' bi.dbo.temp2.bonus,'set @sql_string=@sql_string+' bi.dbo.temp3.salescommission,'set @sql_string=@sql_string+' bi.dbo.temp4.socialinsurance,'set @sql_string=@sql_string+' bi.dbo.temp5.benefits,'set @sql_string=@sql_string+' bi.dbo.temp6.housingotherallowance,'set @sql_string=@sql_string+' bi.dbo.temp7.headcount,'set @sql_string=@sql_string+' bi.dbo.temp1.iyperiod,'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod,bi.dbo.temp1.iyear from bi.dbo.temp1 left join bi.dbo.temp2 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp2.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp2.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp2.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp3 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp3.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp3.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp3.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp4 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp4.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp4.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp4.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp5 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp5.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp5.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp5.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp6 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp6.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp6.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp6.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp7 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp7.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp7.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp7.iyperiod;'set @sql_string=@sql_string+' drop table bi.dbo.temp1;'set @sql_string=@sql_string+' drop table bi.dbo.temp2;'set @sql_string=@sql_string+' drop table bi.dbo.temp3;'set @sql_string=@sql_string+' drop table bi.dbo.temp4;'set @sql_string=@sql_string+' drop table bi.dbo.temp5;'set @sql_string=@sql_string+' drop table bi.dbo.temp6;'set @sql_string=@sql_string+' drop table bi.dbo.temp7;'exec(@sql_string)print @sql_string/*团队建设表 tdjs*/set @sql_string='select ' +''''+ '团队建设表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+' isnull(md,0.00) as '+''''+'training'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp1 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660208'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '团队建设表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'teambuilding'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp2 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660207'+''''+';'set @sql_string=@sql_string+'select ' +''''+ '团队建设表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+'isnull(md,0.00) as '+''''+'employeeuniforms'+''''+','set @sql_string=@sql_string+'iyperiod,iperiod,iyear into bi.dbo.temp3 from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'660210'+''''+';'set @sql_string=@sql_string+' insert into bi.dbo.tdjs(gltype,companyname,training,teambuilding,employeeuniforms,iyperiod,iperiod,iyear)'set @sql_string=@sql_string+' select bi.dbo.temp1.gltype,bi.dbo.temp1.companyname,'set @sql_string=@sql_string+' bi.dbo.temp1.training,'set @sql_string=@sql_string+' bi.dbo.temp2.teambuilding,'set @sql_string=@sql_string+' bi.dbo.temp3.employeeuniforms,'set @sql_string=@sql_string+' bi.dbo.temp1.iyperiod,'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod,bi.dbo.temp1.iyear from bi.dbo.temp1 left join bi.dbo.temp2 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp2.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp2.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp2.iyperiod'set @sql_string=@sql_string+' left join bi.dbo.temp3 on 'set @sql_string=@sql_string+' bi.dbo.temp1.iperiod=bi.dbo.temp3.iperiod and bi.dbo.temp1.iyear=bi.dbo.temp3.iyear and bi.dbo.temp1.iyperiod=bi.dbo.temp3.iyperiod;'set @sql_string=@sql_string+' drop table bi.dbo.temp1;'set @sql_string=@sql_string+' drop table bi.dbo.temp2;'set @sql_string=@sql_string+' drop table bi.dbo.temp3;'exec(@sql_string)print @sql_string/*折旧表 zjb*/set @sql_string='insert into bi.dbo.zjb (gltype,companyname,dyzj,ljzj,gdzcjz,zjclgdzc,iyperiod,iperiod,iyear)'set @sql_string=@sql_string+' select ' +''''+ '折旧表' +''''+ ' as gltype,'+''''+rtrim(@db_company_name)+''''+ ' as companyname,'set @sql_string=@sql_string+' a.mc as '+''''+ '当月折旧'+''''+',a.me as '+''''+'累计折旧'+''''+',(a.me-b.me) as '+''''+'固定资产净值'+''''+' ,a.md-a.mc as '+''''+'新增或处理固定资产'+''''+','set @sql_string=@sql_string+' a.iyperiod,a.iperiod,a.iyear 'set @sql_string=@sql_string+' from (select mc,md,me,iyear,iperiod,iyperiod from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'1601'+''''+') a,'set @sql_string=@sql_string+' (select mc,md,me,iyear,iperiod,iyperiod from '+rtrim(@db_code)+'.dbo.gl_accsum where ccode='+''''+'1602'+''''+') b 'set @sql_string=@sql_string+' where a.iyperiod=b.iyperiod and a.iperiod=b.iperiod and a.iyear=b.iyear;'exec(@sql_string)print @sql_stringfetch next from table_cursor into @db_code,@db_company_nameendclose table_cursordeallocate table_cursor