I am having SalesAccount Table and I have a job that calls the storedprocedure who deletes old records later than 3 years. Table retains 3 year data. Jobs runs every day. I want to create a log table which stores all the records who are deleted successfully and the date last deleted.
[ID] [int] IDENTITY(1,1) NOT NULL,
[DateTimeStamp] [smalldatetime] NULL,
[BankerInfo] [varchar](100) NULL,
[Notes] [varchar](250) NULL
delete from SalesAccount
where ID in
select ID from SalesAccount
where DateTimeStamp <= (DATEADD(YEAR , -3 , GETDATE()))
Question: how to create a log table which contains the information:
id, details(comma delimited list of all records that are deleted), count of records that are deleted, loggedon(date of deletion). Insert this information in log table after job runs to delete data.