Search code examples
sql-servernetezzainformatica-powercenter

Match entries between two different tables on two different servers record-by-record

I have two different databases located on two different servers.

One is SQL Server while the other is Netezza.

I wish to check if the entries on the SQL Server are identical to the entries on my Netezza. I wish to do this record by record.

I want the output to tell me which entries exist in SQL Server but DO NOT exist in Netezza.

I am trying to use Informatica PowerCenter to create a mapping, but I am stumped.

Solution

  • Use an Joiner transformation to join those tables. Select Netezza table as Master and SQL server table as Detail and select the join type as Detail Outer Join.

    Now in the output from joiner, you will get all rows from SQL server, and only matching rows from Netezza table. If the record is not present in Netezza, Netezza fields will come as NULL. You can check only one Netezza key field in an expression transformation afterwards to determine if the records is present in Netezza.