Skip to main content
A
ayushim
August 17, 2021
Question

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

  • August 17, 2021
  • 10 replies
  • 0 views

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 .

    10 replies

    selvakumark
    selvakumark
    Participating Frequently
    August 17, 2021

    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.

    A
    ayushimAuthor
    August 17, 2021

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

    peter.lewis
    peter.lewis
    Participating Frequently
    August 17, 2021

    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?

    A
    ayushimAuthor
    August 17, 2021

    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.

    A
    ayushimAuthor
    August 17, 2021

    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. 

    Terms & ConditionsAccessibility statement