Link to home
Start Free TrialLog in
Avatar of 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

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

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?
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

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

"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).

so if the columns are the same a simple:

select * from table2
select * from table1

should do it.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial