sql - 丑陋的大光标

Question

我正在从一个大约有 1000 列的表中填充一个大约有 15 列的表。我需要从大 table 上抢时间。该时间分为分钟和小时 [rn-min] 和 [rn-hr]，我需要在新表中以 am/pm 格式显示它们。该表由一家外部公司填写，因此我不能对其进行太多更改，我确实让他们放入了一个转移的列中供我检查。它又大又慢，我只需要几列，而且有很多重复/相似的行。无论如何，我都是用大 table 做小 table 。我写了一个光标，它很慢，我想知道是否有更好的方法来做到这一点。我不能只使用简单的插入(选择列)，因为我想更改时间和日期的存储方式。谢谢，感谢任何帮助或建议

declare data CURSOR READ_ONLY FORWARD_ONLY
for   
    select [raID],
            (otherfields),
            CAST([RA-rent-mm] as varchar(2)) + '/' + CAST([RA-rent-dd] as varchar(2)) + '/' +
            CAST([RA-Rent-CC] as varchar(2)) + CAST([RA-RENT-YY] as varchar(2)) [Date_Out],
            CAST([RA-Rtrn-mm] as varchar(2)) + '/' + CAST([RA-Rtrn-dd] as varchar(2)) +
            '/' + CAST([RA-Rtrn-CC] as varchar(2)) + CAST([RA-Rtrn-YY] as varchar(2)) [Date_In],
            CAST([RA-RENTAL-HOURS] as varchar(2)),
            CAST([RA-RENTAL-Minutes] as varchar(2)),
            CAST([RA-RTRN-HOURS] as varchar(2)),
            CAST([RA-RTRN-MINUTES] as varchar(2)),
            (other fields)
      from table_name
     where Transfered is null
       and [RA-rtrn-mm] != 0 --this keeps me from getting the duplicate/similar rows, once this doesn't equal 0 there aren't anymore rows so I just grab this one

declare @sql as varchar(max)
declare @raID int;
    (other fields),
declare @rentDate varchar(8);
declare @rtrnDate varchar(8);
declare @rentHours varchar(2);
declare @rentMinutes varchar(2);
declare @rtrnHours varchar(2);
declare @rtrnMinutes varchar(2);
    (other fields)

open data
    fetch next from data into 
        @raID,
        (other fields),
        @rentDate ,
        @rtrnDate ,
        @rentHours ,
        @rentMinutes ,
        @rtrnHours ,
        @rtrnMinutes ,
        (other fields),
    while @@FETCH_STATUS = 0
    begin
        set @rentMinutes = left('0' + @rentMinutes,2);--padding with 0 if minutes is 1-9
        set @rtrnMinutes = left('0' + @rtrnMinutes,2);

        --turning the varchar times into a time then back to varchar with correct am/pm notation
        declare @rentT time = @rentHours + ':' + @rentMinutes;
        declare @rtnT time = @rtrnHours + ':' + @rtrnMinutes;
        declare @rentTime varchar(7) = convert(varchar(15),@rentT, 100);
        declare @returnTime varchar(7) = convert(varchar(15),@rtnT, 100);

        --print @rentTime;
        set @sql = 'INSERT other_tbl_name(raID, (other fields), Date_Out, Date_In, Time_Out, Time_In,    (other fields))
                values ('+cast(@raID as varchar(max))+', (other fields),'''+@rentDate+''',
                            '''+@rtrnDate+''', '''+@rentTime+''', '''+@returnTime+''',
                            (other fields))';

        --exec(@sql)
        print @sql

        --need a way to make sure the insert worked before updating
        --need to update transferred to keep from updating the same info

        declare @update as varchar(max) = '
        UPDATE Capture.icokc_data
           SET Transfered = 1
         WHERE [raID] = '+cast(@raID as varchar(10))

        --exec(@update)
        --print @update

        fetch next from data into 
            @raID,
            (other fields)
            @rentDate ,
            @rtrnDate ,
            @rentHours ,
            @rentMinutes ,
            @rtrnHours ,
            @rtrnMinutes ,
            (other fields)
    end

close data;
deallocate data;

Answer 1

为什么不批量插入它，并在选择中转换日期和时间？

像这样:

INSERT other_tbl_name(raID, (other fields), Date_Out, Date_In, Time_Out, Time_In, (other fields))
select 
[raID],
(otherfields),
CAST([RA-rent-mm] as varchar(2)) + '/' + CAST([RA-rent-dd] as varchar(2)) + '/' + CAST([RA-Rent-CC] as varchar(2)) + CAST([RA-RENT-YY] as varchar(2)) [Date_Out],
CAST([RA-Rtrn-mm] as varchar(2)) + '/' + CAST([RA-Rtrn-dd] as varchar(2)) + '/' + CAST([RA-Rtrn-CC] as varchar(2)) + CAST([RA-Rtrn-YY] as varchar(2)) [Date_In],
CONVERT(varchar(15),DATEADD(minute, [RA-RENTAL-Minutes], DATEADD(hour, [RA-RENTAL-HOURS], '00:00')), 100) as [Time_out],
CONVERT(varchar(15),DATEADD(minute, [RA-RTRN-MINUTES], DATEADD(hour, [RA-RTRN-HOURS], '00:00')), 100) as [Time_in],
(other fields)
from table_name
where Transfered is null
and [RA-rtrn-mm] != 0

UPDATE Capture.icokc_data
SET Transfered = 1
WHERE [raID] IN
(
select 
[raID]
from table_name
where Transfered is null
-- and [RA-rtrn-mm] != 0  -- not sure about this one
)