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

Hi, 

Do you need to do this at the Appian level (or) at the database level?

If it is at the Appian level - Consider you're having 2 variables with the data to insert into 2 tables. Then you can create an expression rule to remove the elements of the 1st array which is already present in the second array.

If it is at the Database level - You can use a temp table to compare the values present in Table 2 & then insert based on the result.

Is this a one time thing or something that you need to do programatically / as part of a process? Also do you need to preserve the existing rows in table 2 or can you just truncate the whole table and add all the new rows?

Table 2 already exists. We just need to add the value of column 2 in new row in table 2 if  value of column 1 (table 1) is not present among the row values of column 2 (table 2).

And we need to permanently store these rows.

We need to add the new row in table 2 only if column 1 (table 1) value does not exists among values of column 2 (table 2) .

Let me explain you with one example:

Table 1: Emp_Details (Emp_Id, Emp_Name, Emp_DOB)

Table 2: Emp_Address (Address_Id, Emp_Name, Emp_Address)

Now it should match the value of Emp_Name in both the tables and if no matching is there, It should enter a new row in Emp_Address table having Emp_Name as Emp_Name in Emp_Details table and Emp_Address as blank. 

Ok that makes more sense, but I think the part we're missing is about why you're doing this. Is it a one time migration? Is this an Appian process that you expect to run once per day? Depending on the answer to this I'd suggest much different solutions. Like the other poster said, if it's a one time activity, I'd suggest just doing this on the database as a single migration. If this is on the Appian side, you'll likely need to query all the data from both tables and use a!forEach() in an expression rule to iterate through the items and update items that don't exist in table 2.

This is not a one time activity.

We are implementing IDP in our process. So if pdf read by IDP is having Emp_Name, that name will store in Emp_Details table.

And now it will match this Emp_Name with Emp_Name in Emp_Address table. If it already exists there, it will do nothing. If not it will insert new row in Emp_Address table with this Emp_Name value and Emp_ Address column will be blank in this new row.

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!

I have tried to implement this by creating two seperate expression rules that are fetching list of Emp_Name from both the table i.e.:

rule!Get_EmpDetails

load(
  local!final,
  local!batch: rule!Get_EmpDetails(),
  local!header: rule!Get_EmpAddressDetails(),
  local!matching: a!forEach(
    items:local!batch,
    expression: with(
      local!val: fv!item,
      a!forEach(
        items: local!header,
        expression: if(exact(fv!item,local!val),"",a!save(local!final,local!header))
      ))
  ),local!final
      
)

rule!Get_Emp_AddressDetails

Then I have created one expression rule for matching these Emp_Name but gettng null as output.

Attaching the code of expressio rule creted for matching the Emp_Name from both tables.

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
    )
  ),
  ...
)