How to insert value in database table on the basis of matching condition in two table column values.

Question

Hi, 
 
 I have below query: 
 
 Suppose, 
 We are having two database tables : Table 1 and Table 2 
 And having column with same name and same type of values in both the tables: column 1 (Table 1) and column 2 (Table 2) 
 We have to match the values of column 1 with column 2 and if same value does not exist in column 2, we need to insert new row in table 2 with that value. 
 We have to create this functionality from scratch. We are just having two table with data. So I can't provide any code here. 
 
 Please help me to understand how can we achieve this .

Peter Lewis · Answer

Ok makes sense, thanks for the context! In a case like this then I would keep it simple. After the IDP process stores a value in the Emp_Details table, I'd suggest creating a script task that contains a query that returns data from the Emp_Address table. Specifically, you should add a filter to return rows where the employee name matches the name from the Emp_Details table. Then add a gateway directly after this script task. If the query returns no results, then go to a Write to Data Store node and insert a row that contains only the employee name. If the query does return results, then skip the Write to Data Store node since the value already exists!

Peter Lewis · Answer

So one issue with your current expression is that the a!save() on line 11 won't work - you can only execute a save in an interface component, not a local variable. 
 Also, do you have to query all of the address details to do a comparison? Why can't you just query the addresses that match your employee details by running the query directly against address? Something like this: 
 a!localVariables(
 local!employee: rule!Get_EmpDetails(),
 local!address: a!queryEntity(
 entity: cons!ADDRESS_DSE
 pagingInfo: a!pagingInfo(
 startIndex: 1,
 batchSize: 1,
 ),
 filter: a!queryFilter(
 field: "Emp_Name",
 operator: "=",
 value: local!employee.Emp_Name
 )
 ),
 ...
)