C-NET :: PROJECTS

cat mssql-mass-delete.md

MSSQL Mass Delete

Posted

To avoid holding up everything else when deleting massive amounts of data from an SQL Server table, you can use the TOP() function to delete the data in chunks.

SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP(1000) FROM [dbo].[mytable]
WHERE timestampUtc < '2015-05-29'
END
sql

The "SELECT 1" primes the @@ROWCOUNT variable, so the while loop will run. Yes, there are indeed prettier ways of doing this, but it doesn't really matter.

Comments