Duplicates Accross Multiple DB

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

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicates Accross Multiple DB

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2010-07-28 : 18:51:31

I need to look for duplicates across multiple databases. These db's come in every month from different

sources. I can't for the life of me figure out how to get the duplicate records - this is what I have so far:

SELECT AcctNo, COUNT(*)AS TotalCount
FROM DB1.dbo.Table1
GROUP BY FileName
HAVING COUNT(*) > 1

which, I know, is not much - but everything else I've tried is, well, basically garbage. Anyone have any

ideas?

Sample:
DB1
Table1
AcctNo
1
2
3

DB2
Table1
AcctNo
1
3
4
5

Results expected: 1, 3

Thanks

ann
Posting Yak Master

220 Posts

Posted - 2010-07-28 : 19:04:07

correction:

what I have is:

SELECT AcctNo, COUNT(*)AS TotalCount
FROM DB1.dbo.Table1
GROUP BY AcctNo
HAVING COUNT(*) > 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 19:15:44

SELECT AcctNo, COUNT(*)AS TotalCount
FROM (SELECT ... FROM DB1.dbo.Table1 UNION ALL SELECT ... FROM DB2.dbo.Table1 UNION ALL ...) t
GROUP BY AcctNo
HAVING COUNT(*) > 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ann
Posting Yak Master

220 Posts

Posted - 2010-07-28 : 19:22:16

That worked beautiful!

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 23:18:30

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ann
Posting Yak Master

220 Posts

Posted - 2010-07-29 : 16:33:18

ok - hate to bug, really try figuring this out myself before ever posting, but I'm stuck ... AGAIN! I want to be able to pull multiple fields when I'm looking for my duplicates, I can figure that out fine with one db:

select AcctNo, FName, Lname from someTable
where exists
(
SELECT AcctNo, COUNT(*)AS TotalCount
FROM DB1.dbo.Table1
GROUP BY AcctNo
HAVING COUNT(*) > 1
)

but of course, I'm working with multiple databases- and the solution provided works great:

SELECT AcctNo, COUNT(*)AS TotalCount
FROM (SELECT ... FROM DB1.dbo.Table1 UNION ALL SELECT ... FROM DB2.dbo.Table1 UNION ALL ...) t
GROUP BY AcctNo
HAVING COUNT(*) > 1

except I cannot get the muliple fields working when I'm using multiple databases.

Any help is appreciated - thanks

Kristen
Test

22859 Posts

Posted - 2010-07-30 : 05:49:01

[code]
select AcctNo, FName, Lname
from someTable AS T
JOIN
(
SELECT AcctNo, COUNT(*)AS TotalCount
FROM (SELECT ... FROM DB1.dbo.Table1 UNION ALL SELECT ... FROM DB2.dbo.Table1 UNION ALL ...) t
GROUP BY AcctNo
HAVING COUNT(*) > 1
) AS X
ON X.AcctNo = T.AcctNo
[/code]

ann
Posting Yak Master

220 Posts

Posted - 2010-07-30 : 14:26:21

Thanks for the response, but that does not work - Invalid Object name 'someTable'

I used the 'someTable' in my example because I have no idea what table to use since I'm getting the fields from a duplicte count on external vendor db's that get attached on a monthly basis. I have to find the dup's and handle them before they can be imported into the main db and then detached. There can be anywhere from 3 - 11 db's that I need to scan through on any given month

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources