September 18, 2009 at 5:45 pm
I have a table that stores computer audit inventory data from a program called
winaudit. It has the following relevant columns:
AuditID: common number for each audit session
Computer: Computer name
ItemName: Contains a subject line of sorts for Itemvalue1
ItemValue1: Contains computer name (1 per audit) or some other data
I need a procedure that will locate duplicate data in ItemVaule1 where ItemName contains the data "Computer Name". I then need it to delete all rows in the table where Computer = "Computer name" from ItemValue1 and AuditID is less than the most current one.
I have a view setup already that shows me all the Computer Names as well as the auditid. I also have a stored procedure that can delete all records older than x amount of days, but this a little more advanced than I usually mess with.
Could someone please offer some code on how I should proceed.
Thank you,
September 18, 2009 at 11:18 pm
bdudley (9/18/2009)
I also have a stored procedure that can delete all records older than x amount of days, but this a little more advanced than I usually mess with
Hi,
Post your sp, so that will make fine turn to possible more advances.
September 18, 2009 at 11:27 pm
This is my SP for deleting rows more than x days old:
CREATE PROCEDURE [dbo].[PurgeWinAudit]
@DaysOld Int
AS
delete FROM dbo.winaudi2
where datediff(dd,DateTimeDB,getdate())>@DaysOld
GO
This is my view statement:
SELECT ItemValue1, DateTimeDB, AuditID
FROM dbo.WinAudi2
WHERE (ItemName = 'Computer Name')
Thanks
September 18, 2009 at 11:51 pm
The PURGEWINAUDIT is enough to maintain the x days records,
And to find out the duplicate rows, use this
SELECT ItemValue1, DateTimeDB, AuditID
FROM dbo.WinAudi2
WHERE (ItemName = 'Computer Name')
group by ItemValue1, DateTimeDB, AuditID
having count(*) > 1
September 19, 2009 at 12:47 am
Thank you very much. I will try this out as soon as possible and let you know how it works. Your assistance is very much appreciated.
September 19, 2009 at 1:51 pm
I tried this out today. The procedure worked until it got to the line "having count(*) > 1". When that line is inserted, I do not receive any results. I kind of understand what the command was supposed to do, but shouldn't it specify count by AuditID.
Thank you.
September 20, 2009 at 9:10 pm
Hi,
Your queries to locate the duplicate records in the table,
Ref: http://support.microsoft.com/kb/139444
And the articles by the chris cubley
http://www.sqlservercentral.com/articles/Miscellaneous/findinganddeletingduplicatedata/1075/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply