数据库中数据导入前的验证并且转换代码.

if object_id('sp_insert_data','p')is not nulldrop proc sp_insert_datagocreate proc sp_insert_dataasbegin    begin try       begin tran       declare @message1 varchar(max)       declare @message2 varchar(max)       declare @message3 varchar(max)       declare @message4 varchar(max)       declare @message5 varchar(max)       declare @message6 varchar(max)       --验证临时表里的数据，如果商品编码有重复的则不通过验证。         ;with cte1 as       (   select 商品编码 from Tempdata   group by 商品编码   having(count(*)>1)       )       select @message1=stuff((       select ','+'['+商品编码+']' from cte1 for xml path('')),1,1,'')+'临时表里的这些数据重复，请重新导入！'       if exists(          select * from (               select count(1)as a from (   select 商品编码 from Tempdata   group by 商品编码   having(count(*)>1)   )b   )c where a>0       )        raiserror(@message1,16,1)       --验证临时表和业务表里面有没有重复的数据。       ;with cte2 as       (   select * from (   select ItemCode from CS_Product_1   union all   select 商品编码 from Tempdata       )b   group by Itemcode   having(count(*)>1))select @message2=stuff((        select ','+'['+Itemcode+']' from cte2 for xml path('')),1,1,'')+'这些数据在临时表里和业务表里有重复，请删除这些数据' if exists( select * from( select count(1)a from(   select * from (   select ItemCode from CS_Product_1   union all   select 商品编码 from Tempdata       )b   group by Itemcode   having(count(*)>1)   )c)d where a>0)raiserror(@message2,16,1)   --查看临时表里的系列编码在清单中是否存在   ;with cte3 as   (   select distinct 系列编码 from (   select  a.系列编码,b.desc0   from tempdata a  left join   (  select *   from  CS_Master_2  where vguid='3B759E71-1C31-4EBC-A421-1F4FB93C6342'  )b on a.系列编码=b.DESC0 )b where desc0 is null  )   select @message3=stuff((  select ','+ '['+系列编码+']' from cte3 for xml path('')),1,1,'')+'这些系列编码在清单表里不存在，请加入清单！'      if exists(      select a from (        select count(*) a from (  select 系列编码 from (   select  a.系列编码,b.desc0   from tempdata a  left join   (  select *   from  CS_Master_2  where vguid='3B759E71-1C31-4EBC-A421-1F4FB93C6342'  )b on a.系列编码=b.DESC0 )b where desc0 is null)c)d where a>0      )      raiserror(@message3,16,1)            --查看临时表里的品类编码在清单中是否存在      ;with cte4 as(       select distinct 品类编码 from (   select  a.品类编码,c.desc0   from tempdata a  left join   (  select *   from  CS_Master_2  where vguid='94256240-DB64-4DDB-8CC2-53BC4608CDDC'  )c on a.品类编码=c.DESC0  )b where desc0 is null      )      select @message4=stuff((      select ','+'['+品类编码+']' from cte4 for xml path('')),1,1,'')+'这些品类编码在清单表里不存在，请加入清单！'            if exists(      select a from(      select count(1)a from(         select 品类编码 from (   select  a.品类编码,c.desc0   from tempdata a  left join   (  select *   from  CS_Master_2  where vguid='94256240-DB64-4DDB-8CC2-53BC4608CDDC'  )c on a.品类编码=c.DESC0  )b where desc0 is null)a)b where a>0      )       raiserror(@message4,16,1)       --查看临时表里的用途在清单中是否存在       ;with cte5 as(  select distinct 用途 from (  select  a.用途,d.desc0  from tempdata a  left join   (  select *   from  CS_Master_2  where vguid='A50B7E2A-841F-4F2E-A41B-B11EE7A4DD9F'  )d on a.用途=d.DESC0)b   where desc0 is null       )       select @message5=stuff((       select ','+'['+用途+']' from cte5 for xml path('')),1,1,'')+'这些用途在清单表里不存在，请加入清单！'       if exists(       select a from (       select count(*)a from (  select  a.用途,d.desc0  from tempdata a  left join   (  select *   from  CS_Master_2  where vguid='A50B7E2A-841F-4F2E-A41B-B11EE7A4DD9F'  )d on a.用途=d.DESC0)b   where desc0 is null)b where a>0       )       raiserror(@message5,16,1)       --查看临时表里的细分在清单中是否存在       ;with cte6 as(       select distinct 细分 from (   select  a.细分,e.desc0   from tempdata a   left join   (  select *   from  CS_Master_2  where vguid='3B759E71-1C31-4EBC-A421-1F4FB93C6342'  )e on a.细分=e.DESC0  )c where desc0 is null)select @message6=stuff((select ','+'['+细分+']' from cte6 for xml path('')),1,1,'')+'这些细分在清单表里不存在，请加入清单！'if exists(  select a from (select count(*)a from (   select  a.细分,e.desc0   from tempdata a   left join   (  select *   from  CS_Master_2  where vguid='3B759E71-1C31-4EBC-A421-1F4FB93C6342'  )e on a.细分=e.DESC0  )c where desc0 is null)d where a>0) raiserror(@message6,16,1)        --开始插入数据         insert into CS_Product_1(ItemCode,ItemCode2,ItemDesc0,OracleID,BarCode,BarCode2,BarCode3,Unit,Volum,Category,Category2,Category3,Category4,FunctionalityDESC,ObjectDESC,PreservationDESC,UseMethodDESC,Published,PublishedDate,Photo,CreDate,CreUser,VGUID,Step,VMDTIME,VMDUSER,DelFlag        )  select 商品编码,旧编码,商品名称,ORACLEID,商品条码,商品条码2,商品条码3,单位,容量,系列编码1,品类编码1,用途1,细分1,产品功效,使用对象,保存方法,使用方法,发布,发布时间,图片名称,getdate(),'sysadmin',newid(),'使用顺序',getdate(),'sysadmin','0'  from (   select a.*, b.mastercode 系列编码1, c.mastercode 品类编码1, d.mastercode 用途1, e.mastercode 细分1   from tempdata a  left join   (  select *   from  CS_Master_2  where vguid='3B759E71-1C31-4EBC-A421-1F4FB93C6342'  )b on a.系列编码=b.DESC0  left join   (  select *   from  CS_Master_2  where vguid='94256240-DB64-4DDB-8CC2-53BC4608CDDC'  )c on a.品类编码=c.DESC0  left join   (  select *   from  CS_Master_2  where vguid='A50B7E2A-841F-4F2E-A41B-B11EE7A4DD9F'  )d on a.用途=d.DESC0  left join   (  select *   from  CS_Master_2  where vguid='3B759E71-1C31-4EBC-A421-1F4FB93C6342'  )e on a.细分=e.DESC0 )b    commit tranend trybegin catch   rollback tran   declare @message varchar(max)   set @message=convert(varchar(500),Error_Message())   raiserror(@message,16,1)  end catchend--exec sp_insert_data