topic Re: SQL Server Multiple Insert/Update row by row in Talend Studio

SQL Server Multiple Insert/Update row by row

Anonymous — Wed, 26 Oct 2016 10:36:01 GMT

Hi,
I started using Talend recently and I have the following problem:
I need to Insert/Update row by row a SQL Server's table (tab2) from data unloaded from another Table (tab1). At the end of every single row, I have to update a Flag in tab1.
I would like that the commit was made at each line so that, if an error occurs, all rows have already been processed are not to be reprocessed.
the Schema is very simple, but I don't know how to link the components
Unload Tab1 -------> Insert/Update Row1 Tab2 ---> Update Row1's Flag Tab1 ---> Commit Row1 --> Start with Row2
How can I do this?
Very Thanks and sorry for my bad English
Claudio

Re: SQL Server Multiple Insert/Update row by row

Anonymous — Wed, 26 Oct 2016 13:35:14 GMT

Are both the tables on the same database?

Re: SQL Server Multiple Insert/Update row by row

Anonymous — Wed, 26 Oct 2016 13:43:29 GMT

Yes, the table are on the same DB
Claudio

Re: SQL Server Multiple Insert/Update row by row

Anonymous — Wed, 26 Oct 2016 14:18:45 GMT

Cool. Then can't you just run this whole process as a simple Stored procedure on the database end. it will be faster.
If you go by row by row processing then your entire process will become very slow. I am not sure how many rows are you processing.
As such there is a batch size option present tMSSQLOutput component. Please specify this batch size as 1 if you want to have a commit count of 1.

Re: SQL Server Multiple Insert/Update row by row

Anonymous — Wed, 26 Oct 2016 14:42:51 GMT

Thank you!
I'm not sure to have understand... Can I call a Stored procedure from Talend? With which component?
This project have a few record per day, and the customer wants to be done in Talend
Thanks again

Re: SQL Server Multiple Insert/Update row by row

Anonymous — Thu, 27 Oct 2016 05:05:49 GMT

Yes you can using tMSSQLSP component. In case you will have few records per day you can follow this approach.
tMSSQLConnection-->tMSSQLInput1-->tFlowtoIterate-->tMSSQLOUTOUT1-->tMSSQLOUTPUT2-->tCOMMIT
1. Create connection
2. Read from tab1 (Make sure to use tMSSQLConnection as connection)
3. Iterate through rows read
4. Insert in tab2 (Make sure to use tMSSQLConnection as connection)
5. Update Tab 1 (Make sure to use tMSSQLConnection as connection)
6. Commit ((Make sure to use tMSSQLConnection as connection))

Re: SQL Server Multiple Insert/Update row by row

Anonymous — Thu, 27 Oct 2016 08:24:52 GMT

Thank You Sankalp, really precious!