query to find out duplicate records in different databases

  • 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)

  • 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!

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

    do we need to use intersect?

  • 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!

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

    Column is empname(need to exclude from selct *)

  • 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!

  • This was removed by the editor as SPAM

  • 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)

  • 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

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

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