Filtering data from Oracle and SQL Server

[Anonymous]

Hi all,

I'm trying to update data from an Oracle Database to a SQL Server Database.
If data is already registered to SQL Server, i don't want to register this data again.

I did my workspace like this : (First capture)

I configured my TMap like this : (Second capture)

I think my mistake is the Inner Join. I don't know if i'm doing it well.

Any help is greatly appreciated.

Regards.

[Anonymous]

Same problem but now sql server table has values, oracle table is full of null values.

I've a primary key on Nivnat from oracle but not on nivnat from sqlserver. Nivnat is the link for  my join. if my sql server table is main, my join can't work ?

Check my new TMAP config.

Annotation 2019-10-14 172249.png

[TRF]

"oracle table is full of null values" it's normal as soon as you catch only inner join rejects.
This option means you want to get only existing record in SQL Server which don't exist in Oracle - so it doesn't make sense to try to get values from this table.

[Anonymous]

"This option means you want to get only existing record in SQL Server which don't exist in Oracle"

         - But i want to get only existing record in Oracle which don't exist in SQL Server

[TRF]

Sorry again.
So your main table should be Oracle and SQL Server the lookup. tMap options are right (catch only rejected records).

[Anonymous]

I don't know if i'm using the good method.

  I want to ckeck line by line from my oracle table.

   with my primary key from oracle table, i want to find the good line in my sql server table.

     with my filterrow, i compare every field to check if there is the same values or not.

       if it's not the same, i save, if it's, i skip.

[TRF]

If you want to compare every fields between both tables, you can build the tMap inner join with all fields if datatypes are identical.
Thanks to "catch inner join reject" as soon as you have a difference for one field, the record will be rejected ans you will be able to do what you want with the target table.

[Anonymous]

So, if i understand well, this one should work well ? or did i have to put every columns from row1 to row2 like row1.nivnat ?

Annotation 2019-10-15 120452.png

[TRF]

With this one, you'll catch only records from row1 where NIVNAT doesn't match with any value from row2 or H_DATEFIN is not null.
If you want to catch all records from row1 where at least one of the 6 fields doesn't match with the corresponding one from row2, you need to do what you do for NIVNAT for all the fields.
Also, remember that as soon as you are using an inner join and decide to catch "inner join rejects", all fields populated from row2 will remain null. That's make sense because you'll have 1 output record for each record from row1 which doesn't have correspondance in row2. Right?

[Anonymous]

---

@TRF wrote:
With this one, you'll catch only records from row1 where NIVNAT doesn't match with any value from row2 or H_DATEFIN is not null.

    Ok.

---

@TRF wrote:
If you want to catch all records from row1 where at least one of the 6 fields doesn't match with the corresponding one from row2, you need to do what you do for NIVNAT for all the fields.

   I did it like this : (understand how to put image into my text )

---

@TRF wrote:
Also, remember that as soon as you are using an inner join and decide to catch "inner join rejects", all fields populated from row2 will remain null. That's make sense because you'll have 1 output record for each record from row1 which doesn't have correspondance in row2. Right?

---

   I'll show you my 2 tables, may be easier to understand :

      MSSQL :

      Oracle :

   My Inner join should give me 5th and 6th line cause they are unregistered in my mssql ?

---

The return in the black circle should be 2 ?

[TRF]

I think you've got 0 row because of date fields which are different from Oracle and SQL Server. I suggest you to use the appropriate conversion in the SQL queries to get both as text with the same format.