query to find out duplicate records in different databases

Question

Post reply

query to find out duplicate records in different databases

mcfarlandparkway

SSCertifiable

Points: 7678
More actions
December 16, 2016 at 10:36 am

#316792

Hello,
I have a table,where it sits in 7 different databases. it has 7 different country information.
I want to find duplicate records from all the databases how to write the query?
eg: dbo.emp this table structure same in all 7 databases (Note - all these databases are in same server)
December 18, 2016 at 9:58 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

it depends on what you define as a duplicate; a few columns, or every column, including identities and CreatedDates?

also, is a duplicate in two databases, or must be in ALL seven?

EXCEPT and INTERSECT are really handy here to data like this.

here's a crappy example:

--same data in two tables; anybody working for both companies?

SELECT ColumnList FROM [CocaCola].[dbo].[emp]

INTERSECT

SELECT ColumnList FROM [Pepsi].[dbo].[emp]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mcfarlandparkway SSCertifiable Points: 7678 More actions · Answer 2

All the column data should be same in all 7 databases. - basically select *..from table

do we need to use intersect?

Lowell SSC Guru Points: 323482 More actions · Answer 3

mcfarlandparkway (12/16/2016)
All the column data should be same in all 7 databases. - basically select *..from table
do we need to use intersect?

you said find duplicates in the first post.

INTERSECT finds items that are the same.

if you want to find things that are NOT the same, you would use EXCEPT, so the same style of example, but using EXCEPT, and SELECT * or SELECT ColumnList where appropriate;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mcfarlandparkway SSCertifiable Points: 7678 More actions · Answer 4

I want to exclude one column from select list how to do this?

Column is empname(need to exclude from selct *)

Lowell SSC Guru Points: 323482 More actions · Answer 5

mcfarlandparkway (12/16/2016)
I want to exclude one column from select list how to do this?
Column is empname(need to exclude from selct *)

my example contained the word [ColumnList] as a placeholder for your real columns.

just write out the columns...you can copy paste from a query results is probably the easiest.

SELECT Column1,Column2,Column3

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

mcfarlandparkway (12/16/2016)
Hello,
I have a table,where it sits in 7 different databases. it has 7 different country information.
I want to find duplicate records from all the databases how to write the query?
eg: dbo.emp this table structure same in all 7 databases (Note - all these databases are in same server)

What are you going to do once you find the duplicates?

Also, do any of the tables have an IDENTITY column on them? It would be really helpful if you'd post the CREATE TABLE statement for one of the tables along with all constraints and indexes. That's pretty easy to do if your scripting options are setup correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SQLisAwesome Old Hand Points: 398 More actions · Answer 7

Can you create a Temporary table and insert all the records into the temporary table and remove duplicates using the CTEs (Common Table Expressions)?

CREATE TABLE #employee (RowID INT IDENTITY(1,1), Country VARCHAR(10))

--ASSUMING YOU ARE INSERTING FROM MULTIPLE DATABASES

INSERT INTO #employee(Country)

VALUES ('CANADA'),('CANADA'),('CANADA'),('UK'),('US'),('FRANCE')

GO

SELECT * FROM #employee

GO

WITH Employee_CTE

AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY COUNTRY ORDER BY COUNTRY ) AS RowNum , *

FROM #employee

)

SELECT COUNTRY

FROM Employee_CTE

WHERE RowNum = 1

DROP TABLE #employee

GO