Home Forums SQL Server 2014 Development - SQL Server 2014 Cursor to Delete data RE: Cursor to Delete data

  • November 5, 2013 at 12:46 pm

    #1664224

    Something like this might work, but you should test the correct numbers depending on your server.

    DECLARE @i int = 1

    WHILE @i > 0

    BEGIN

    DELETE TOP (1000000) MyTable

    WHERE Table1.DateTimeColumn < dateadd(ww, -2, getDate())

    SET @i = @@ROWCOUNT

    BEGIN END

    Or if you have a trigger on the table that might alter @@ROWCOUNT value.

    WHILE EXISTS (

    SELECT TOP 1 1

    FROM MyTable

    WHERE Table1.DateTimeColumn < dateadd(ww, -2, getDate())

    )

    BEGIN

    DELETE TOP (1000000) MyTable

    WHERE Table1.DateTimeColumn < dateadd(ww, -2, getDate())

    SET @i = @@ROWCOUNT

    BEGIN END

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2