Reputation: 11120
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
Reputation: 6764
Create an AFTER DELETE
trigger on the table. You can query deleted
for the count.
SELECT COUNT(*)
FROM deleted
Upvotes: 0
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