One Star

SQL Server Multiple Insert/Update row by row

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
6 REPLIES
One Star

Re: SQL Server Multiple Insert/Update row by row

Are both the tables on the same database? 
One Star

Re: SQL Server Multiple Insert/Update row by row

Yes, the table are on the same DB
Claudio
One Star

Re: SQL Server Multiple Insert/Update row by row

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.
One Star

Re: SQL Server Multiple Insert/Update row by row

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 Smiley Happy
Thanks again
One Star

Re: SQL Server Multiple Insert/Update row by row

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))
One Star

Re: SQL Server Multiple Insert/Update row by row

Thank You Sankalp, really precious!