why the execute sql script is not working in kettle

Question

I am using kettle to get data from one table (t1) and joining execute sql script tool (t2) and then making an insert/update in the same table (t1)

Here's my transform

table input tool

select stud_id,mark from student; 

execute sql Script

select s.stud_id,ifnull(m.mark,0) as mark from mark as m inner join student as s on (s.stud_id=m.stud_id) where s.student_id='?'

fields: stud_id

insert/update tool

• table: student
• check: stud_id=stud_id
• update: mark=mark

When I run this, it's successfully running but the values are not inserted or updated in target table.

Answer 1

If the Execute SQL Script step is the one I think your referring to, it doesn't generate output. It's for forming up a bunch of SQL statements in your transform and running them individually. I'm not in front of PDI right now, but I believe the way to run a dynamic SQL statement and add its output to your data flow is the Execute Dynamic SQL step.

Never the less, in your case I would use a Database Join step instead. This step prepares the statement and just re-executes the query plan for each row that arrives in the transform, substituting data into parameter markers. Much more performance friendly.

Answer 2

The Execute SQL step is for INSERTS, DELETES, UPDATES, CREATES, etc.

For a SELECT query, you would use Table Input.