Saroj13
asked on
How to create a log table of all deleted records Sql Server 2008?
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.
SalesAccount Table:
[ID] [int] IDENTITY(1,1) NOT NULL,
[DateTimeStamp] [smalldatetime] NULL,
[BankerInfo] [varchar](100) NULL,
[Notes] [varchar](250) NULL
stored procedure:
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.
thanks
SalesAccount Table:
[ID] [int] IDENTITY(1,1) NOT NULL,
[DateTimeStamp] [smalldatetime] NULL,
[BankerInfo] [varchar](100) NULL,
[Notes] [varchar](250) NULL
stored procedure:
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.
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Would you please help me in
This will generate all the deleted rows in the Log Table. HELP--- I want Columns in the Log Table [ID(primary key), List of deleted IDS from SalesAccountTable in the comma delimited list, Count of deleted ids, Date of execution.
delete from SalesAccount
output deleted.ID, deleted.DateTimeStamp, ... into SalesAccountLog ( ID, DateTimeStamp, ... )
where ID in
(
select ID from SalesAccount
where DateTimeStamp <= (DATEADD(YEAR , -3 , GETDATE()))
)
This will generate all the deleted rows in the Log Table. HELP--- I want Columns in the Log Table [ID(primary key), List of deleted IDS from SalesAccountTable in the comma delimited list, Count of deleted ids, Date of execution.
delete from SalesAccount
output deleted.ID, deleted.DateTimeStamp, ... into SalesAccountLog ( ID, DateTimeStamp, ... )
where ID in
(
select ID from SalesAccount
where DateTimeStamp <= (DATEADD(YEAR , -3 , GETDATE()))
)
ASKER
All the ID's that are deleted from the salesaccount table in the comma delimited list and Count of how many items are deleted.