t-sql 2012 table differences

• dianerstein 8713

  SSCrazy

  Points: 2197

  More actions

  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?

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  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.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  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