CODEWITHSUNDEEP
sql-server-2008t-sql
James A Mohler
James A Mohler

Reputation: 11120

Logging count of DELETED records

When I try to run this

DECLARE @by varchar(20) = 'Myself'

INSERT INTO dbo.MiscLog(LogType, onTable, Message, CreateBy)
   SELECT 
      'Info', 'dbo.History', 
      'There are ' + CONVERT(varchar(20), COUNT(ID)) + ' old records being purged.', @by
   FROM 
       (DELETE FROM dbo.History
        OUTPUT deleted.ID
        WHERE keyDate < dateadd(y, -7, getDate())) H

I get

Msg 5322, Level 15, State 1, Line 5
An aggregate function is not allowed in the SELECT clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.

Upvotes: 0

Views: 95

Answers (2)

HardCode
HardCode

Reputation: 6764

Create an AFTER DELETE trigger on the table. You can query deleted for the count.

SELECT COUNT(*)
FROM   deleted

Upvotes: 0

coge.soft
coge.soft

Reputation: 1674

Try inserting a copy of the deleted records into a temp table and then run your COUNT() aggregation on the temp table.

SELECT *
INTO #tempLog
   FROM 
       (DELETE FROM dbo.History
        OUTPUT deleted.ID
        WHERE keyDate < dateadd(y, -7, getDate())) H

INSERT INTO dbo.MiscLog(LogType, onTable, Message, CreateBy)
   SELECT 
      'Info', 'dbo.History', 
      'There are ' + CONVERT(varchar(20), COUNT(ID)) + ' old records being purged.', @by
FROM #tempLog

Upvotes: 1

Related Questions