t-sql在没有游标的情况下处理大量sql行

Question

我需要循环处理1600万行SQL数据。由于性能问题，我被告知不要为此使用光标。我从WHILE循环开始，但我不知道如何使用WHILE循环来获得表中的下一行。请在下面查看我的进度。如何获取每行的AccountNames列？

DECLARE @rowId INT = 1;
DECLARE @RowCnt BIGINT = 0;
DECLARE @source varchar(8000);
DECLARE @current_source varchar(8000);
DECLARE @search varchar(8000) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE @replacement varchar(8000) = '22233344455566677778889999';
DECLARE @Converted varchar(8000) ;
SELECT @RowCnt = COUNT(0) FROM dbo.AccountNames;
select @source = AccountNumber FROM dbo.AccountNames where AccountNumber > ' ' order by Accountnumber;
WHILE @rowId <= 2
BEGIN
declare @i int, @iMax int;
set @iMax = len(@search);
set @i = 1;
while @i <= @iMax
begin
set @Converted = replace(@source, substring(@search, @i, 1), substring(@replacement, @i, 1));
set @i = @i + 1;
end
select @source = AccountNumber FROM dbo.AccountNames where AccountNumber > @current_source order by Accountnumber ;
print @source;
set @current_source = @source;
Set @rowId = @rowId + 1;
END

-编辑感谢您的快速评论。我将根据评论部分的要求提供更多细节。

我需要从AccountNames中的每条记录中获取AccountNumber字段，将其转换为代码中显示的键盘值，然后将该值插入到一个不同的表中，该表包含AccountNumber及其等效键盘。如果游标对于这么大的表来说是可以的，那么我会很感激有人向我展示正确的语法。

-编辑2我无法使用translate，因为我使用的是SQL Server 2016。

Answer 1

当；翻译"；答案显然是更优雅的解决方案，若因为版本兼容性而不能使用它，可以用嵌套的replace语句替换它。它的运行速度应该仍然比游标或while循环快得多，并且结果可以用于INSERT等设置操作。我在SQL 2014上测试了这个，它有效，所以2016年应该不会给你带来任何问题

with cteSampleData as (
SELECT * 
FROM (VALUES ('CXE-34-A5'), ('52Q-A6-brxt'), ('kqed-pc-12ALE')
) as Accts(AccountNumber)
), cteTranslated as ( 
SELECT AccountNumber
, REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(AccountNumber, 
'A', '2'), 'B', '2'), 'C', '2'), 
'D', '3'), 'E', '3'), 'F', '3'), 
'G', '4'), 'H', '4'), 'I', '4'), 
'J', '5'), 'K', '5'), 'L', '5'), 
'M', '6'), 'N', '6'), 'O', '6'), 
'P', '7'), 'Q', '7'), 'R', '7'), 'S', '7'), 
'T', '8'), 'U', '8'), 'V', '8'), 
'W', '9'), 'X', '9'), 'Y', '9'), 'Z', '9'
) as AcctTrans
FROM cteSampleData
) --INSERT INTO DestTable (RawAcct, TranslatedAcct) 
SELECT AccountNumber, AcctTrans
FROM cteTranslated

编辑：输出如下：

AccountNumber	AcctTrans
CXE-34-A5	293-34-25
52Q-A6-brxt	527-26-2798
kqed-pc-12ALE	5733-72-12253

您可以用一个SELECT语句来完成这整件事：

DECLARE @search varchar(8000) =      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
DECLARE @replacement varchar(8000) = '2223334445556667777888999922233344455566677778889999';
SELECT  AccountNumber,
TRANSLATE(AccountNumber, @search, @replacement) AS TranslatedAcctNum
FROM    dbo.AccountNames 
WHERE   Accountnumber > 'DDDDDD' 
order by AccountNumber;

它也可以很容易地转换为一个INSERT语句。

作为额外的好处，它的运行速度将提高10-1000倍。

(注意：如果没有任何测试数据，我无法测试它，所以可能会有拼写错误，但它们应该很容易修复。(

我找到了一种使用光标的方法。我将在该代码的最终版本中插入一个插入语句，而不是打印帐号。

DECLARE @rowId INT = 1;
DECLARE @AccountNum varchar(8000);
DECLARE @c cursor;
DECLARE @search varchar(8000) =      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
DECLARE @replacement varchar(8000) = '2223334445556667777888999922233344455566677778889999';
DECLARE @Converted varchar(8000) ;

SET @c = cursor LOCAL FAST_FORWARD FOR SELECT AccountNumber 
FROM dbo.AccountNames 
where Accountnumber > 'DDDDDD' 
order by AccountNumber;
OPEN @c;
fetch next from @c into @AccountNum;
While @@fetch_status = 0
BEGIN
print @AccountNum;
declare @i int, @iMax int;
set @iMax = len(@search);
set @i = 1;
while @i <= @iMax
begin
set @AccountNum = replace(@AccountNum, substring(@search, @i, 1), substring(@replacement, @i, 1));
set @i = @i + 1;
end
print @AccountNum; --put insert statement here
fetch next from @c into @AccountNum;
set @rowId = @rowId + 1; --remove this test code after code is finished
if @rowId > 1
begin
break
end
END
close @c;
deallocate @c;