Execute SQL Queries in Talend

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.

Execute SQL Queries in Talend 1

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.

Execute SQL Queries in Talend 2

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.

Execute SQL Queries in Talend 3

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.

Execute SQL Queries in Talend 4

We use the existing Connection that we created in the Talend tDBConnections component.

Execute SQL Queries in Talend 5

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.

Execute SQL Queries in Talend 6
"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.

Execute SQL Queries in Talend 7

Within the Management Studio, you can see the result.

Execute SQL Queries in Talend 8