• 使用游标、存储过程、pivot 三种方法导入数据


    --使用游标循环
    if (exists (select * from sys.objects where name = 'Base_RecordTend_Test'))
        drop proc Base_RecordTend_Test
    go
    CREATE PROCEDURE Base_RecordTend_Test  
    AS   
        declare @ID nvarchar(20),
         @Worker  nvarchar(50),
         @WorkerName  nvarchar(50),
         @ReceiveType  nvarchar(50),
         @sql  nvarchar(max)
        declare mycursor cursor for  select top 1  id,Receive_Worker,Receive_WorkerName,Receive_filetype from Archive_Detail_Receive where  Receive_filetype is not null
        open mycursor      
        fetch next from mycursor into @ID,@Worker, @WorkerName, @ReceiveType   
        
        while (@@fetch_status=0)  
        begin     
       set @sql =' insert  into Base_RecordTend( RT_WorkerNum,RT_workerName,RT_FileId, ['+@ReceiveType+'])values('''+@Worker+''','''+@WorkerName+''','''+@ID+''','''+@ReceiveType+''')'
         EXEC sp_executesql @sql 
         print   @sql 
         fetch next from mycursor into @ID,@Worker, @WorkerName, @ReceiveType   
        end   
        close mycursor  
        DEALLOCATE mycursor   
    GO 
    
     exec Base_RecordTend_Test 
     
      
      
     --循环插入数据
     
     if(exists (select * from sys.objects where name='BaseRecordTend_XH'))
     drop proc BaseRecordTend_XH
     go
     create proc BaseRecordTend_XH
     as
     begin
      declare @ID nvarchar(20),
         @Worker  nvarchar(50),
         @WorkerName  nvarchar(50),
         @ReceiveType  nvarchar(50),
         @sql  nvarchar(max),
         @count int,
         @number int ;
         
        -- select top 1 @count=count(*) from Archive_Detail_Receive where  Receive_filetype is not null 
         set @count=5;
         set @number=0;
        
         if(@count is not null and @count<>0)
         begin
             while @number <@count 
             begin
               select @number = @number +1;----每循环一次循环条件+1
               select @ID=AR.id,@Worker=AR.Receive_Worker,@WorkerName=AR.Receive_WorkerName,@ReceiveType=AR.Receive_filetype
               from(select  ROW_NUMBER() over (order by id) 'rowindex',*  from Archive_Detail_Receive where  Receive_filetype is not null) as AR where AR.rowindex = @number;
               set @sql =' insert  into Base_RecordTend( RT_WorkerNum,RT_workerName,RT_FileId, ['+@ReceiveType+'])values('''+@Worker+''','''+@WorkerName+''','''+@ID+''','''+@ReceiveType+''')'
               EXEC sp_executesql @sql  
             end 
             
         end
     end
     go
      
     exec BaseRecordTend_XH
     
    
    
    
    --pivot 行转列
     select Receive_Worker,Receive_WorkerName,id, 
      (case when cast( ltrim([1-1]) as int) > 0 then '1-1' when cast(ltrim([1-1]) as int)=0 then null end) as [1-1] ,
      (case when cast( ltrim([2-1]) as int) > 0 then '2-1' when cast(ltrim([2-1]) as int)=0 then null end) as [2-1] ,
      (case when cast( ltrim([3-1]) as int) > 0 then '3-1' when cast(ltrim([3-1]) as int)=0 then null end) as [3-1] ,
      (case when cast( ltrim([4-1-1]) as int) > 0 then '4-1-1' when cast(ltrim([4-1-1]) as int)=0 then null end) as [4-1-1] ,
      (case when cast( ltrim([4-2-1]) as int) > 0 then '4-2-1' when cast(ltrim([4-2-1]) as int)=0 then null end) as [4-2-1] ,
      (case when cast( ltrim([4-3-1]) as int) > 0 then '4-3-1' when cast(ltrim([4-3-1]) as int)=0 then null end) as [4-3-1] ,
      (case when cast( ltrim([4-4-1]) as int) > 0 then '4-4-1' when cast(ltrim([4-4-1]) as int)=0 then null end) as [4-4-1] ,
      (case when cast( ltrim([5-1]) as int) > 0 then '5-1' when cast(ltrim([5-1]) as int)=0 then null end) as [5-1] ,
      (case when cast( ltrim([6-1]) as int) > 0 then '6-1' when cast(ltrim([6-1]) as int)=0 then null end) as [6-1] ,
      (case when cast( ltrim([7-1]) as int) > 0 then '7-1' when cast(ltrim([7-1]) as int)=0 then null end) as [7-1] ,
      (case when cast( ltrim([8-1]) as int) > 0 then '8-1' when cast(ltrim([8-1]) as int)=0 then null end) as [8-1] ,
      (case when cast( ltrim([9-1-1]) as int) > 0 then '9-1-1' when cast(ltrim([9-1-1]) as int)=0 then null end) as [9-1-1] ,
      (case when cast( ltrim([9-2-1]) as int) > 0 then '9-2-1' when cast(ltrim([9-2-1]) as int)=0 then null end) as [9-2-1] ,
      (case when cast( ltrim([9-3-1]) as int) > 0 then '9-3-1' when cast(ltrim([9-3-1]) as int)=0 then null end) as [9-3-1] ,
      (case when cast( ltrim([10-1]) as int) > 0 then '10-1' when cast(ltrim([10-1]) as int)=0 then null end) as [10-1]  
      from 
      ( select top 100 id,Receive_Worker,Receive_WorkerName,Receive_filetype  from Archive_Detail_Receive  )as tend 
      PIVOT 
      ( COUNT( Receive_filetype )
       for Receive_filetype in ([1-1], [2-1], [3-1],[4-1-1],[4-2-1],[4-3-1],[4-4-1],[5-1],[6-1],[7-1],[8-1],[9-1-1],[9-2-1],[9-3-1],[10-1] )) as a
    
     
     
     
     
  • 相关阅读:
    webapi帮助文档swagger
    后台任务hangfire
    EF6 DbModelBuilder
    .net跨平台分析软件
    EF迁移命令
    NuGet Package Explorer
    ghost blog 中文资料
    IDependency自动注册autofac
    Abp zero 示例运行
    AutoMapperExtension
  • 原文地址:https://www.cnblogs.com/panmy/p/5580386.html
Copyright © 2020-2023  润新知