RSS
 

Posts Tagged ‘update loop’

SQL Delete Operation using While Loop

28 Apr 
DECLARE @EACH_UPDATE_COUNT INT
 
SET @EACH_UPDATE_COUNT=1000 --YOU CAN CHANGE THIS VALUE
 
SET ROWCOUNT @EACH_UPDATE_COUNT
 
WHILE 1=1
      BEGIN
            DELETE tmp
            IF @@ROWCOUNT = 0 BREAK
      END
 
No Comments

Posted in English, SQL Server

 

SQL Update Operation using While Loop

07 Apr

For faster update of tables which have huge data, we can use a while loop as given below;

DECLARE @EACH_UPDATE_COUNT INT
DECLARE @LOOP_COUNT BIGINT
 
SET @EACH_UPDATE_COUNT=1000 --YOU CAN CHANGE THIS VALUE
SET @LOOP_COUNT=0 --DO NOT CHANGE
 
WHILE 1=1
BEGIN
WITH tmp AS (
SELECT TOP (@EACH_UPDATE_COUNT) * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY ID_COLUMN ASC) AS ROWID,* FROM MY_TABLE) T
WHERE ROWID > @LOOP_COUNT*@EACH_UPDATE_COUNT AND ROWID <= (@LOOP_COUNT+1)*@EACH_UPDATE_COUNT
)
UPDATE tmp SET UPDATE_COLUMN = 'NEW_VALUE' --OR YOU CAN ALSO MAKE A DELETE OPERATION HERE
--DELETE tmp
IF @@ROWCOUNT = 0 BREAK
SET @LOOP_COUNT=@LOOP_COUNT+1
END
 
No Comments

Posted in English, SQL Server