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

Hi,

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
Saroj13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
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
VALUES(ValueForField1,ValueForField2,ValueForField3,.....ValueForFieldN)

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
GO
on the line after Insert if I remember correctly.
0
Pooja Katiyar VermaCommented:
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
    FOR DELETE
AS
<your queries>
GO

* For comma delimited list ex.:
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
@Countrec
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
GO
0
Scott PletcherSenior DBACommented:
0
Saroj13Author Commented:
ALTER PROCEDURE [dbo].[usp_DeleteRecords]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      SET NOCOUNT ON
            
      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
0
SStoryCommented:
I'd do a
BEGIN TRAN t1;
INSERT INTO CustomerAcctLog
(column-names)
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()))
      )

END TRAN

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.