Dataset node Insert/Update node is used to insert, update, or delete data from Jiffy Table using a query.
You can write complex queries Cdata format for insert/update/delete operations. The query editor supports ANSI SQL INSERT, UPDATE, DELETE queries.

Query Tab of Dataset Insert/Update Node

1. Double click the Dataset node and click the Query tab.
2. Specify query to be executed in the Query field

• No semicolon is required at the end of the query.
• Provide Table name inside double curly braces {{ }}, for example, {{Table1}}.
• Refer inline table names using dot notation, for example, {{MainTable.InnerTable1.InnerTable2}}.
• Refer columns in inline Tables using dot notation, within square brackets, for example, [InnerTable1.InnerTable2.Column2]
• Refer rows in inline Tables using indexes (starting with 0).

In Colleges table, there are three columns; Name, Address, and Courses. Inside the inline table Courses, there are three columns; Subject, Teacher, and Students. Inside the Students inline table, there are two columns; SID and Score

Sample Insert Query

• Insert values in the Colleges main table and inner tables for single row:
  INSERT into {{Colleges}} ( Name, Address, [Courses.0.Subject], [Courses.0.Teacher], [Courses.0.Students.0.SID],[Courses.0.Students.0.Score]) values ('John Smith', ‘Evans Mills’, ‘Physics’, ‘Peter’, ‘Student1’, ‘100’)
• Insert a record directly to inner table:
  INSERT into {{Colleges.Courses}} ( P_id, Subject, Teacher, [Students.0.Sid],[Students.0.Score]) values ('61bd9aadbc2d1b6854749XXX', 'Mathematics', ‘Peter’, ‘Student33’, ‘98’)

Sample Update Query

• Update the Value Peter Smith in the Name column of the table Colleges:
  UPDATE {{Colleges}} set Name='Peter Smith'
• Update a single row in main table and inline tables:
  Update {{College}} set Name='Peter Smith', [Courses.0.Subject]='Physics', [Courses.0.Teacher]='John', [Courses.0.Students.0.Sid]='Student1',[Courses.0.Students.0.Score]='100' where UUID='61c42402ee31f30d8c763dxyz'

Sample Delete Query

• Delete the records from the Colleges for the specified UUID:
  DELETE from {{Colleges}} where UUID=‘61c42402ee31f30d8c763dxyz’
• Delete rows from the Colleges table with Execution state is New:
  DELETE from {{Colleges}} where [Execution State]=‘New’

• INSERT
• UPDATE
• DELETE

Processing Type

The query can be processed in two ways:

1. Row Wise Processing: Records in the dataset are processed row by row.

   Insert the Value John Smith to the Name column of the table TravelDetailstable:
   INSERT into {{TravelDetailstable}} ( Name) values ('John Smith')

   
2. Bulk Processing: Records in the dataset are processed in bulk.
   Use Bulk Processing when you want to update multiple records from the input Datatable at once.
   Specify the DataTable to which you want the records to be updated/inserted/deleted in the Query. DataTables are mapped from the previous node. To know more about DataTable, click here.

   Insert the Values from the Datatable input_table_name to the Name column of the table TravelDetailstable:
   INSERT into {{TravelDetailstable}} ( Name) values ('${input_table_name}')

   

Properties of the Dataset Insert/Update Node

1. Navigate to Properties and enter Name and Description.
2. Enable Mark run Failure on Node Fail, Continue on no result? options as required.

Result of Execution of Dataset Insert/Update Node

After executing the task, the Result of Execution window displays the following tabs:

• Output: Status and details of the updated rows are displayed.
  
• Run Info: Run ID, Sequence Number, Iteration ID, Iteration Start Time, Iteration End Time, Iteration Time(in seconds), and Total Node Execution Time(in seconds) are displayed.