execute query or a stored proc for every 500 rows based on id
Hi, The scenario is as follows: 1. I have a table in Server A with 100,000 rows. In this table i have a field whose values are passed as a parameter for stored proc on Server B. 2. I can only pass 500 values to the stored proc on Server B. I have to combine all the result sets into a single table. Can anyone suggest me the right task to be used for this. Thanks.
April 21st, 2011 3:09pm

Do you mean you want to loop through the table on server A until you processed all the data and then the resulting data needs to be merged into a table? So why not to use a ForEach Loop to go through the table on server A as many times as needed (and this piece of logic you do not tell, but it seems to me based just on the threshold of 500 at a time)? I am also not sure if that ID is numeric so we can increment it. So the ForEach will be based on a variable in any case and the most suitable example I can think of right now is probably this: http://www.rad.pasfu.com/index.php?/archives/18-Foreach-Loop-based-on-Variable-SSIS.htmlArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 21st, 2011 3:20pm

Hi what the error that you are getting ? what i have understood (correct me if i am wrong) is that 1- you have to loop through each record in SERVER A on tbl A and pass it on to a SP (stored procedure) in SERVER B 2- the results of all SP will have to be in one table my suggestion Loop 1 --- Get the PK from list from tblA that will represent the 100,000 records SQL Exec - Pass the variable to call the SP in Server B , The result will be saved in a staging table in server b - End Loop - see if Package Transaction and check point will help http://technet.microsoft.com/en-us/library/cc304421.aspx good luckSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
April 21st, 2011 3:50pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics