We download data from our vendor each night into a staging database. Once we're sure the download was successful, we move the staging data to production.
We use SSIS on SQL 2014 to connect to the Oracle servers that our vendor uses, and sometimes that process randomly truncates the last character of a field. Not the last character of every record, but random records, here and there. It's very frustrating.
Anyway, I typically match tables together on their primary key to look for truncation. In one table, the truncation occurred in the primary key.
So what I wanted to do was to join production with the staging on the key and show mismatched records. Sort of like a left join unioned with a right join. Enter the Full outer join.
SELECT a.PrimaryKey ,b.PrimaryKey
FROM Staging.Table a
FULL OUTER JOIN Production.TAble b
ON a.PrimaryKey=b.PrimaryKey
Results
Nice
| a.PrimaryKey | b.PrimaryKey |
| Red | Null |
| Null | Re |
Nice
