Automating Full Outer Joins

Recently I helped out on a project where a company was moving to a new CRM system. Part of the validation process was to compare identical old vs new tables, column by column, and report any discrepancies between each value…. and there were dozens of tables and hundreds of columns.

So I decided to automate the process of creating the FULL OUTER JOIN scripts to perform this task and report back a comparison column for each field to state if the data matches. Overall, the script simply uses the sys.tables and sys.columns to dynamically create the SQL statement based upon the user established join condition and a few other details. This will become apparent once you dig into the scripts.

You can find my scripts in the below GitHub repository along with a PDF that provides the instructions for each of my scripts. I also provide test data and samples to run, which will make understanding them that much easier. Feel free to modify them to fit your needs!

Database Tips and Tricks

Happy Coding!

Leave a Reply