The Talend tDBRow component helps us to write or Execute SQL queries. We can use this Talend tDBRow to perform DML operations or execute any SQL Query that returns no result.
To demonstrate the Execute DML statements or queries in Talend, we use two tables. The first one is the reference table.
We use the above table and write the merge statement on the below table. Within the Merge Statement, we perform Insert, Update, and Delete Operations.
Talend Execute SQL Queries Example
We used the tDBConnection to connect with SQL Server. Next, drag and drop the tDBRow from palette to Job designer.
Within the tDBRow Components tab, you can see the available options. It is the same as the tDBInput, but we have to use the Query window to write our own SQL Query.
We use the existing Connection that we created in the Talend tDBConnections component.
Here, we wrote the Merge Statement to perform DML operations. I suggest you to refer the SQL Merge article to understand everything about this query. We used the same tables, same query, and bit of change in the data to make it easy.
"MERGE [Merge_Table] AS mrg USING (SELECT * FROM [Employee]) AS emp ON mrg.EmpID = emp.EmpID WHEN MATCHED AND mrg.[YearlyIncome] <= 50000 THEN DELETE WHEN MATCHED AND mrg.[YearlyIncome] >= 80000 THEN UPDATE SET mrg.[YearlyIncome] = mrg.[YearlyIncome] + 35000 WHEN NOT MATCHED THEN INSERT ([FirstName], [LastName], [Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate]) VALUES(emp.[FirstName], emp.[LastName], emp.[Education] ,emp.[Occupation] ,emp.[YearlyIncome] ,emp.[Sales] ,emp.[HireDate]);"
Let’s run this Execute DML Queries in Talend job.
Within the Management Studio, you can see the result.