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 the palette to the 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.
For the connection, we use the existing Connection that we created in the Talend tDBConnections component.
For the tDBRow_1, 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 a 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 the Talend job.
Within the Management Studio, you can see the result.
