CODEWITHSUNDEEP
sql
user3380585
user3380585

Reputation: 173

how to run tsql query in batch -- delete 5000 rows at a time and commit

Can you please tell me how to run a query in chunks . I AM TRYNING to delete duplicate records based on MyKey. how to tune this" 

WITH cte AS
 ( SELECT [ID] ,[F2] ,[F3] ,[F8] ,[F14] ,[F29] ,[F31] ,[F43] ,[F44] ,[F45] ,[F46] ,[F47] ,[F48] ,[F49] ,[F50] ,[F52] ,[F53] ,[F54] ,[F55] ,[F56] ,[F57] ,[F58] ,[F59] ,[F63] , row_number() 
OVER(PARTITION BY MyKey ORDER BY MyKey 
) 
AS [rn] 
FROM [myDB].[dbo].[myTable] 
) 
DELETE cte WHERE [rn] > 1

"

Upvotes: 0

Views: 221

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Wouldn't you want the final query to be:

DELETE cte WHERE [rn] > 1 and rn < 5000

Do you need help writing a while loop as well?

Upvotes: 1

Related Questions