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

if object_id('sp_insert_data','p')is not null
drop proc sp_insert_data
go
create proc sp_insert_data
as
begin
    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 tran
			end try
			begin catch
			   rollback tran
			   declare @message varchar(max)
			   set @message=convert(varchar(500),Error_Message())
			   raiserror(@message,16,1)  
			end catch
		end




--exec sp_insert_data