Avatar of tselectro
tselectro
 asked on

Display table difference

I have to identical tables:
Table1: TagID (Primary key), PlantName,[Object],[Type]
Table2: TagID (Primary key), PlantName,[Object],[Type]

I want to list rows that exist in Table2, but have different data

Ex
Table1
TagID (PK)      PlantName      Object      Type
KRA.Tag1            KP100            Pump1      A
KRA.Tag2            KP100            Pump2      B
KRA.Tag3            KP101            Motor1      B

Table2
TagID (PK)      PlantName      Object      Type
KRA.Tag1            KP100            Pump1      A
KRA.Tag2            KP100            Pump2      C
KRA.Tag3            KP101            Motor2      C
KRA.Tag4            KP102            Motor1      A (exclude this row, does not exist in Table1)

Result I want:
TableName      TagID (PK)      PlantName      Object            Type
Table1            KRA.Tag2            KP100            Pump1            B
Table2            KRA.Tag2            KP100            Pump1            C (diff)
Table1            KRA.Tag3            KP101            Motor1            B
Table2            KRA.Tag3            KP101            Motor2 (diff)      C (diff)


Someone who can help me with the ms sql syntax?
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Ioannis Paraskevopoulos

SELECT * 
FROM Table1 T1 
JOIN Table2 T2 
ON T1.TagID = T2.TagID
WHERE T1.PlantName <> T2.PlantName 
OR T1.Object <> T2.Object
OR T1.Type <> T2.Type

Open in new window


Giannis
lcohan

"I want to list rows that exist in Table2, but have different data "

I would use new SQL functions EXCEPT or INTERSECT for that matter and just keep in mind if you have text or ntext data columns you will need to convert them to varchar(max) or nvarchar(max).

http://msdn.microsoft.com/en-us/library/ms188055(v=sql.100).aspx

so if the columns are the same a simple:

select * from table2
except
select * from table1

should do it.
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes