How to delete all but the last 100 Call Tickets in a HEAT database using SQL query

Version 1

    Details

    What is the SQL query to delete all but the last 100 Call Tickets in a HEAT database?


    Resolution

    After backing up the full HEAT database, run the following in SQL Management Studio:

    --Run this first--

    --CALLLOG--

    DELETE FROM CallLog

    WHERE CallID NOT IN

    (SELECT TOP 100 [CallID]

      FROM CallLog)

    --Then run the following so no orphaned, unlinked rows exist for remaining Ticket tables--

    --SUBSET--

    DELETE FROM Subset

    WHERE CallID NOT IN

    (SELECT CallID FROM CallLog)

    --DETAIL--

    DELETE FROM Detail

    WHERE CallID NOT IN

    (SELECT CallID FROM CallLog)

    --ASSIGNMENT--

    DELETE FROM Asgnmnt

    WHERE CallID NOT IN

    (SELECT CallID FROM CallLog)

    --JOURNAL--

    DELETE FROM Journal

    WHERE CallID NOT IN

    (SELECT CallID FROM CallLog)