t-sql process large set of SQL rows without cursor-CodePudding

I need to loop through 16 million rows of SQL data. I have been told not to use a cursor for this because of performance issues. I have started with a WHILE loop but I can't figure out how to get the next row in the table using a WHILE loop. See my progress below. How do I get the AccountNames column for each row?

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

-edit Thanks for the super fast comments. I will provide more detail based on the requests in the comments section.

I need to take the AccountNumber field from each record in AccountNames, convert it to the keypad value shown in my code, then insert that value into a different table that holds the AccountNumber and its Keypad equivalent. If cursors are OK for a table this large then I would appreciate someone showing me the proper syntax for that.

CodePudding user response：

You can do this whole thing with one SELECT statement:

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;

Which can also be easily turned into a single INSERT statement.

As an added benefit, it will run 10-1000 times faster.

(Note: Without any test data, I could not test it, so there may be typos, but they should be easily fixable.)

CodePudding user response：

I found a way to do it with a cursor. Instead of printing the account number, I will put an insert statement into the final version of this code.

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;