--如果有查询结果，说明存在不一致的情况，请修改表结构，否则数据转移作业不能成功执行
--将住院病人明细记录从1表转到2表（例如从VAJ1转到VAJ2）
--exec HO_HistoryBatchMove_zy
--把原离线表数据从恢复到新离线表中（例如从VAJ2_dev到VAJ1_2和VAJ2_2）
--Exec HO_HistoryBatchMove_dev

GO
declare @tmpCols Table(fName varchar(20),fNum1 int,fNum2 int,fNum1_2 int,fNum2_2 int,fNum7 int)
  
  insert into @tmpCols(fName,fNum1,fNum2)
  select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end fname
    ,COUNT(1) FCOUNT,0
  from syscolumns a
  join sysobjects b on b.id = a.id
  where b.name in('VAJ1','VAI1','VAK1','VBL1','VAF1','VBI1','CBM1','VBQ1','VBD1','VBG1','DPD1','DPD2','LAB1','LAC1','FAB1','FAC1','VAD1','VAO1','FAF1')
    and b.type = 'U'
  group by b.name
    
  update a
  set a.fNum2 = b.FCOUNT
  from @tmpCols a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name ,COUNT(1) FCOUNT
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ2','VAI2','VAK2','VBL2','VAF2','VBI2','CBM2','VBQ2','VBD2','VBG2','DPD1_2','DPD2_2','LAB2','LAC2','FAB2','FAC2','VAD2','VAO2','FAF2')
          and b.type = 'U'
        group by b.name) b on b.name = a.fName
  update a
  set a.fNum1_2 = b.FCOUNT
  from @tmpCols a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name ,COUNT(1) FCOUNT
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ1_2','VAI1_2','','','VAF1_2','VBI1_2','CBM1_2','VBQ1_2','VBD1_2','','','','','','','','','','')
          and b.type = 'U'
        group by b.name) b on b.name = a.fName
  update a
  set a.fNum2_2 = b.FCOUNT
  from @tmpCols a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name ,COUNT(1) FCOUNT
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ2_2','VAI2_2','','','VAF2_2','VBI2_2','CBM2_2','VBQ2_2','VBD2_2','','','','','','','','','','')
          and b.type = 'U'
        group by b.name) b on b.name = a.fName
  update a
  set a.fNum7 = b.FCOUNT
  from @tmpCols a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name ,COUNT(1) FCOUNT
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ7','VAI7','','','VAF7','VBI7','CBM7','VBQ7','VBD7','','','','','','','','','','')
          and b.type = 'U'
        group by b.name) b on b.name = a.fName
  
  
  select * from @tmpCols where fNum1 <> fNum2 or fNum1 <> fNum1_2  or fNum2 <> fNum2_2  or fNum2 <> fNum7
  
  declare @tmpColLen Table(fName varchar(20),fColN varchar(20),uType1 smallint,ulength1 smallint,uPrec1 smallint,uScale1 int
                       ,uType2 smallint,ulength2 smallint,uPrec2 smallint,uScale2 int
					   ,uType1_2 smallint,ulength1_2 smallint,uPrec1_2 smallint,uScale1_2 int
					   ,uType2_2 smallint,ulength2_2 smallint,uPrec2_2 smallint,uScale2_2 int
					   ,uType7 smallint,ulength7 smallint,uPrec7 smallint,uScale7 int)
  
  insert into @tmpColLen(fName,fColN,uType1,ulength1,uPrec1,uScale1,uType2,ulength2,uPrec2,uScale2)
  select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end fname
    ,a.name,a.xusertype,a.length,a.prec,a.scale,0,0,0,0
  from syscolumns a
  join sysobjects b on b.id = a.id
  where b.name in('VAJ1','VAI1','VAK1','VBL1','VAF1','VBI1','CBM1','VBQ1','VBD1','VBG1','DPD1','DPD2','LAB1','LAC1','FAB1','FAC1','VAD1','VAO1','FAF1')
    and b.type = 'U'
    
  update a
  set a.uType2 = b.xusertype,a.ulength2 = b.length,a.uPrec2 = b.prec,a.uScale2 = b.scale
  from @tmpColLen a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name,a.name ColN
          ,a.xusertype,a.length,a.prec,a.scale
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ2','VAI2','VAK2','VBL2','VAF2','VBI2','CBM2','VBQ2','VBD2','VBG2','DPD1_2','DPD2_2','LAB2','LAC2','FAB2','FAC2','VAD2','VAO2','FAF2')
          and b.type = 'U'
       ) b on b.name = a.fName and b.ColN = a.fColN 


    update a
  set a.uType1_2 = b.xusertype,a.ulength1_2 = b.length,a.uPrec1_2 = b.prec,a.uScale1_2 = b.scale
  from @tmpColLen a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name,a.name ColN
          ,a.xusertype,a.length,a.prec,a.scale
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ1_2','VAI1_2','','','VAF1_2','VBI1_2','CBM1_2','VBQ1_2','VBD1_2','','','','','','','','','','')
          and b.type = 'U'
       ) b on b.name = a.fName and b.ColN = a.fColN 
 

    update a
  set a.uType2_2 = b.xusertype,a.ulength2_2 = b.length,a.uPrec2_2 = b.prec,a.uScale2_2 = b.scale
  from @tmpColLen a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name,a.name ColN
          ,a.xusertype,a.length,a.prec,a.scale
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ2_2','VAI2_2','','','VAF2_2','VBI2_2','CBM2_2','VBQ2_2','VBD2_2','','','','','','','','','','')
          and b.type = 'U'
       ) b on b.name = a.fName and b.ColN = a.fColN 

	    update a
  set a.uType7 = b.xusertype,a.ulength7 = b.length,a.uPrec7= b.prec,a.uScale7 = b.scale
  from @tmpColLen a
  join (select Case when SUBSTRING(b.name,1,1)='D' then upper(SUBSTRING(b.name,1,4)) else upper(SUBSTRING(b.name,1,3)) end name,a.name ColN
          ,a.xusertype,a.length,a.prec,a.scale
        from syscolumns a
        join sysobjects b on b.id = a.id
        where b.name in('VAJ7','VAI7','','','VAF7','VBI7','CBM7','VBQ7','VBD7','','','','','','','','','','')
          and b.type = 'U'
       ) b on b.name = a.fName and b.ColN = a.fColN 
  
  
  select * from @tmpColLen 
  where uType1 <> uType2 or ulength1 <> ulength2 or uPrec1 <> uPrec2 or uScale1 <> uScale2 
  or uType1_2 <> uType1 or ulength1_2 <> ulength1 or uPrec1_2 <> uPrec1 or uScale1_2 <> uScale1 
  or uType2_2 <> uType2 or ulength2_2 <> ulength2 or uPrec2_2 <> uPrec2 or uScale2_2 <> uScale2 
  or uType1_2 <> uType7 or ulength1_2 <> ulength7 or uPrec1_2 <> uPrec7 or uScale1_2 <> uScale7 
  or uType2_2 <> uType7 or ulength2_2 <> ulength7 or uPrec2_2 <> uPrec7 or uScale2_2 <> uScale7 
  go