Wednesday, 22 July 2015

How To DELETE Records Batch Wise By Using Cursor ?

DECLARE @TblName Varchar(Max), @MSQL Nvarchar(Max)
DECLARE TopTableDelete Cursor
FOR
SELECT TOP 5 Name FROM Sysobjects WHERE xtype = 'U' order by name
OPEN TopTableDelete
FETCH NEXT FROM TopTableDelete INTO @TblName
WHILE @@FETCH_STATUS = 0
BEGIN      
 SET @MSQL = 'DELETE RDYadav FROM
     (SELECT SrlNo = ROW_NUMBER() OVER(ORDER BY GETDATE()),*
       FROM [dbo].['+@TblName +']) RDYadav
       WHERE SrlNo >100000'
 EXEC(@MSQL)    
 --Print @MSQL
 FETCH NEXT FROM TopTableDelete INTO @TblName
END
CLOSE TopTableDelete
DEALLOCATE TopTableDelete

No comments:

Post a Comment