Duplicates across multiple databases

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 2000 Forums

Transact-SQL (2000)

Duplicates across multiple databases

Author

Topic

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 09:35:22

Hi,

Im pretty new to SQL but here is my issue.

I have the following queries that we use:

use ABC001 select co#=(001), fieldvalue from fundinfotable where fieldid = '1'
use ABC002 select co#=(002), fieldvalue from fundinfotable where fieldid = '1'

thats just two databases there is a lot more.

I would like it to display duplicates across all databases.
so if fieldvalue for co# 001 is 1234 and in co#2 there is a fieldvalue of 1234 i want it to display that.

Basically right now we run all these queries for every database and we export everything to a file and then open it in excel. we then format it it so its just co# and fieldvalue and then look for duplicates.

i would like to avoid the whole excel part and just run a query that will display any duplicates of fieldvalue across all databases in "Fundinfotable"

thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 09:39:14

create a view with the unions like you tried with WITH:
select co#=(001), fieldvalue from ABC001..fundinfotable where fieldid = '1'
union all
select co#=(001), fieldvalue from ABC002..fundinfotable where fieldid = '1'
union all
etc...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 09:50:13

I get that part

how do i then get it to display duplicate fieldvalue's. two columns are CO# and Fieldvalue I just wnat to display anything that is shown more than once in the fieldvalue column

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 09:55:50

well if your view show all duplicated data then you perform a grouping operation on the view, no?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 09:57:05

if [co#] and [fieldid] are uniquely constrainted in both ABC001 and ABC002, use the above view something like this...

select [co#]
from view1
group by [co#]
having count(*) > 1

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 10:23:40

ok so say i create a view and its called testview

this gives me an error:
Select * from testview
group by fieldvalue
having count(*) > 1

Column 'testview.co#' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

testview doesn't have any duplicates so it should be blank

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 10:28:56

quote:
Originally posted by QuietRiot

ok so say i create a view and its called testview

this gives me an error:
Select * from testview
group by fieldvalue
having count(*) > 1

Column 'testview.co#' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

testview doesn't have any duplicates so it should be blank

That isn't the same as what was suggested. If you use Select * (Bad idea), you have name each column in all applicable tables in the source within the group by.

Poor planning on your part does not constitute an emergency on my part.

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 10:36:46

i still get the same error

SELECT fieldvalue, Co#
FROM testview
GROUP BY fieldvalue
HAVING ( COUNT(fieldvalue) > 1 )

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 11:08:39

quote:
Originally posted by anonymous1

if [co#] and [fieldid] are uniquely constrainted in both ABC001 and ABC002, use the above view something like this...

select [co#]
from view1
group by [co#]
having count(*) > 1

'
That was suggestion provided. See the to yours below difference?


 i still get the same error

SELECT fieldvalue, Co#
FROM testview
GROUP BY fieldvalue
HAVING ( COUNT(fieldvalue) > 1 )

Assuming you must have fieldvalue AND CO# in the list this may work for you (and I am sure someone will come along with better answer, as this won't be "perfect")

Try this:


Select FieldValue,[CO#]
FROM TestView INNER JOIN (Select [FieldValue],[CO#] COunt(*) as CT
                          FROM TestView 
                          Group by [FieldValue],[CO#]) c
on TestView.FieldValue = c.FieldValue and TestView.[CO#] = x.[CO#]

Group by FieldValue,[CO#]
Where CT > 1

Poor planning on your part does not constitute an emergency on my part.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 11:14:32

another similar solition...
select view1.[c#], view1.[fieldvalue]
from
view1
join
(
select [fieldvalue]
from view1
group by [fieldvalue]
having count(*) > 1
) as dups
on dups.[fieldvalue] = view1.[fieldvalue]

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 11:54:18

this worked!!


select testview.[co#], testview.[fieldvalue]
from
testview
join
(
select [fieldvalue]
from testview
group by [fieldvalue]
having count(*) > 1
) as dups
on dups.[fieldvalue] = testview.[fieldvalue]

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 12:02:46

is the only way of doing this by creating a view? anyway of combining it into 1 item that needs to be run instead of me creating a view then running another query to find dups

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 14:26:54

just put the sql content of the view into a derived table, but then you cannot re-use it in other sql statements and may end up doing copy/paste all over...

select testview.[co#], testview.[fieldvalue]
from
(
select '001' AS [co#], fieldvalue from ABC001..fundinfotable
union all
select '002', fieldvalue from ABC002..fundinfotable
--repeat
) as testview
join
(
select [fieldvalue]
from
(
select '001' AS [co#], fieldvalue from ABC001..fundinfotable
union all
select '002', fieldvalue from ABC002..fundinfotable
--repeat
) as testview
group by [fieldvalue]
having count(*) > 1
) as dups
on dups.[fieldvalue] = testview.[fieldvalue]

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 15:37:06

hmm.. but, I have 50 databases. so if i do it twice there will be like 200 lines of selects and union all

is there an easier way?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-12 : 15:41:52

the easy way WAS the view ;) generate the create view statement using master.dbo.sysdatabases...

select 'create view dbo.textview as ' union all
select 'select ' + char(39) + name + char(39) + ' as [co#], field_value from ' + name + '.dbo.fundinfo union all'
from master.dbo.sysdatabases

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-12 : 20:47:12

sorry,

I'm really new at this. i have no idea how to work with master.dbo.sysdatabases. how do I use it for the entire create view statement.

if it was up to me I would just use the view, but people who know less than me (seriously) will be using this and instead of having them run create view and then a separate query I would like them to just copy and paste and run something and not have to touch anything else.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources