Deleting duplicate records from lots of tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deleting duplicate records from lots of tables

Author

Topic

enlighten
Starting Member

3 Posts

Posted - 2006-08-29 : 00:35:27

Hi All,

So.. I'm a complete newb to SQL stuff.

I managed to find the 'Deleting Duplicate Records' from SQLTeam.com (thanks, by the way!!).. I managed to modify it for one of my tables (one of 14).


-- Add a new column 

Alter table dbo.tblMyDocsSize add NewPK int NULL
go

-- populate the new Primary Key
declare @intCounter int
set @intCounter = 0
update dbo.tblMyDocsSize
SET @intCounter = NewPK = @intCounter + 1

-- ID the records to delete and get one primary key value also
-- We'll delete all but this primary key
select strComputer, strATUUser, RecCount=count(*), PktoKeep = max(NewPK)
into #dupes
from dbo.tblMyDocsSize
group by strComputer, strATUUser
having count(*) > 1
order by count(*) desc, strComputer, strATUUser

-- delete dupes except one Primary key for each dup record
delete	dbo.tblMyDocsSize
from	dbo.tblMyDocsSize a join #dupes d
on	d.strComputer = a.strComputer
and	d.strATUUser = a.strATUUser
where	a.NewPK not in (select PKtoKeep from #dupes)

-- remove the NewPK column
ALTER TABLE dbo.tblMyDocsSize DROP COLUMN NewPK
go

drop table #dupes

Now that I've got that figured out, I need to write the same thing to fix the other 13 tables (with different column info)- and I'll need to run this daily.

Basically I've put together some vbscript that gathers inventory data and drops it into an MSDE db (sorry - goin for 'free' stuff right now). Problem is it has to run daily so that I'm sure to capture computers that turned on at different times etc which ever-increases my database 'till I bounce off the 2GB limit of MSDE.

So the question is, what would be the best way to do this? Can I put the code into a stored procedure that I can execute each day?

Thanks for your help....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 01:03:46

Yes you can!
And even better, schedule a job to run the stored procedure for you, at any given time and at any given interval.

Peter Larsson
Helsingborg, Sweden

enlighten
Starting Member

3 Posts

Posted - 2006-08-29 : 09:41:53

So far I've read a couple of the stored procedure tutorials from here, but I still don't totally know what I need to do...

Can I just add:
CREATE PROCEDURE spMydocsclean

and then paste the rest of the code below, with an 'EXEC spMydocsclean' at the bottom of the procedure?

When I do that, I get 'Incorrect syntax near the word 'alter'....
Thanks for your help...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-29 : 09:54:02

Dont put EXEC spMydocsclean at the bottom of the procedure

Madhivanan

Failing to plan is Planning to fail

enlighten
Starting Member

3 Posts

Posted - 2006-08-29 : 10:45:56

OK, even when I remove the EXEC at the end, it still gives that error.. here's what I've got (I can't even save it right now)

CREATE PROCEDURE spMydocsclean 

-- Add a new column 
-- In real life I'd put an index on it
ALTER TABLE dbo.tblMyDocsSize add NewPK int NULL
go

-- populate the new Primary Key
declare @intCounter int
set @intCounter = 0
update dbo.tblMyDocsSize
SET @intCounter = NewPK = @intCounter + 1

-- ID the records to delete and get one primary key value also
-- We'll delete all but this primary key

select strComputer, strATUUser, RecCount=count(*), PktoKeep = max(NewPK)
into #dupes
from dbo.tblMyDocsSize
group by strComputer, strATUUser
having count(*) > 1
order by count(*) desc, strComputer, strATUUser

-- delete dupes except one Primary key for each dup record
delete	dbo.tblMyDocsSize
from	dbo.tblMyDocsSize a join #dupes d
on	d.strComputer = a.strComputer
and	d.strATUUser = a.strATUUser
where	a.NewPK not in (select PKtoKeep from #dupes)

-- remove the NewPK column
ALTER TABLE dbo.tblMyDocsSize DROP COLUMN NewPK
go

drop table #dupes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources