How to log the count of deleted records in table in sql server?

Posted on 2014-07-18
Medium Priority
Last Modified: 2014-07-31

Every day sql server job runs that  calls the storedproocedure who delete the last 3 years data. I want the StoredProcedure also create a log table who stores the deleted data information(Comma delimited list of all ids of deleted records, count of all deleted items, date when the items deleted)?

How to create a log table in storedprocedure
Question by:Saroj13
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 25

Expert Comment

ID: 40205316
I'm not sure what the question exactedly is.  If you have a stored procedure you can do anything in it.
You could create a table to hold all kinds of logs and then in the stored procedure run an INSERT query
INSERT (fieldname1,fieldname2,fieldname3.....fieldnameN) INTO logtablename

Those values could be whatever you want them to be. You can use variables if you like.
For example, the line above the insert could be:

declare @var varchar(20)
set @var = 'Some Info Here'

You could then use @var as a value in the INSERT above.  So there is no way anyone can answer your question without seeing the stored procedure and knowing what needs to go where.  You'd need to have a log table or create one I'd think. Then the only way you could get a more specific answer is if you provided the structure of that log table. However what I have told you gives you the basic idea.
I think you may have to put
on the line after Insert if I remember correctly.

Expert Comment

by:Pooja Katiyar Verma
ID: 40205341
1. Insert data into log table before deleting data (use same pattern select statement which you are using for delete)

2. You can create Trigger on tables from which data is deleted for above requirement.
Sample trigger syntax:-
CREATE TRIGGER sampleTrigger
    ON database1.dbo.table1
<your queries>

* For comma delimited list ex.:
USE AdventureWorks
Select @Countrec= count (column_name) FROM table_name where <condition>
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr, @Countrec, Getdate() --insert into log table
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40205489

Author Comment

ID: 40205539
ALTER PROCEDURE [dbo].[usp_DeleteRecords]
      -- Add the parameters for the stored procedure here
      DELETE from CustomerAcct
      where ID in
              select ID from CustomerAcct
              where DateTime <= (DATEADD(YEAR , -1 , GETDATE()))

I am deleting records from CustomerAcct tabl. When the records are deleted, CustomerAcctLog table ---insert all the id's in comma delimited list, count of deleted record
LVL 25

Accepted Solution

SStory earned 2000 total points
ID: 40206399
I'd do a
INSERT INTO CustomerAcctLog
SELECT (column-names) from CustomerAcct
where ID in
              select ID from CustomerAcct
              where DateTime <= (DATEADD(YEAR , -1 , GETDATE()))

      DELETE from CustomerAcct
      where ID in
              select ID from CustomerAcct
              where DateTime <= (DATEADD(YEAR , -1 , GETDATE()))


Open in new window

Note you will have to fill in column-names where found above.
The idea is start a transaction that will be all or nothing.  INSERT into the log pulling from a select on the data you will be deleting, then delete it. If both ops don't take place it will rollback.

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question