使用游标、存储过程、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