Post reply

t-sql 2012 table differences

  • January 24, 2017 at 9:16 pm

    #339623

    My goal is to compare data in Table1 in the database called test1 to
    the data in Table1 in the database called test2. Both databases called test1
    and test2 on the same database server called DBT. DBT database server is
    SQL Server 2012. I want to find out what the differences are between the
    2 tables. I want to find out exactly what the differences are.
    The table is defined as:
    Create Table1 (
    username  varchar(20) not null,
    schoolnum varchar(03) not null,
    MainRole  char(01) not null
    )
    Here is example data:
           username      schoolnum  mainRole
            user1           040       Y    
            user2           040       N
            user1           001       Y
            user3           040       Y
    Table1 does not have a key to the table. There can be
    exact duplicate rows in this table.
    Thus can you show me the t-sql 2012 so I can find where the
    differnces between the 2 tables are?

  • January 24, 2017 at 10:28 pm

    #1924782


    SELECT username, schoolnum, MainRole FROM test1.dbo.Table1
    EXCEPT
    SELECT username, schoolnum, MainRole FROM test2.dbo.Table1;

    How to post data/code on a forum to get the best help.

  • January 25, 2017 at 1:49 am

    #1924799

    -- rows which are only in test1

    SELECT username, schoolnum, MainRole,

    rn = ROW_NUMBER() OVER(PARTITION BY username, schoolnum, MainRole ORDER BY (SELECT NULL))

    FROM test1.dbo.Table1

    EXCEPT

    SELECT username, schoolnum, MainRole,

    rn = ROW_NUMBER() OVER(PARTITION BY username, schoolnum, MainRole ORDER BY (SELECT NULL))

    FROM test2.dbo.Table1;

    and

    -- rows which are only in test2

    SELECT username, schoolnum, MainRole,

    rn = ROW_NUMBER() OVER(PARTITION BY username, schoolnum, MainRole ORDER BY (SELECT NULL))

    FROM test2.dbo.Table1

    EXCEPT

    SELECT username, schoolnum, MainRole,

    rn = ROW_NUMBER() OVER(PARTITION BY username, schoolnum, MainRole ORDER BY (SELECT NULL))

    FROM test1.dbo.Table1;

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 3 posts - 1 through 2 (of 2 total)

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