Update variable inside foreach loop

Question

Update variable inside foreach loop

LearnSQL

SSC-Addicted

Points: 407
More actions
July 25, 2022 at 2:33 pm

#4067910
```
.
```
Hi All,
I have a requirement to update set of records to an external environment which is connected via ADO .net
There are set of records in my table which i can form the the update statement as per the requirement which i can connect to external environment.
Currently i am able to do with only static variable without loop , where my records gets updated.
I need to loop through my set of records which I can pass through variable.
Will the variable will update dynamically based on the record which it has in my reference table ?
Could you please pass on your suggestions and thoughts and if any reference link available with similar kind

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Showing us your code may help us help you. Providing some sample data, and an expected output would also make things far easier.

My signature line contains a link on how to do that

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Thom A SSC Guru Points: 99130 More actions · Answer 2

Why are you using a loop at all would be my first question. Also, please don't put your content in a code block (unless it's code of course); it makes it harder to read for us as it forces us to have to keep scrolling back and forth (it would be really nice if you could edit your post to remove it, please).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ed B Hall of Fame Points: 3666 More actions · Answer 3

I don't fully understand what you're saying, but have you tried using a for each loop based on the values of an SSIS object variable? Typically this is used for exporting files or other tasks that need to be done sequentially. Usually sql updates don't require a loop. This is useful reference article, it uses a variable to set the connection string for a data-flow, but you can use it for any variable driven task.

https://www.timmitchell.net/post/2013/05/28/using-the-ssis-object-variable-as-a-result-set-enumerator/

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 4

LearnSQL wrote:

Hi All,
I have a requirement to update set of records to an external environtment which is connected via ADO .net
There are set of records in my table which i can form the the update statement as per the requirement which i can connect to external evironment.
Currently i am able to do with only static variable without loop , where my records gets updated.
I need to loop through my set of records which I can pass through variable.
Will the variable will update dynamically based on the record which it has in my reference table ?
Could you please pass on your suggestions and thoughts and if any reference link avaialable with similar kind

Post the code and explain what the variable is about. Otherwise, the standard answer of "you should be able to do this without a loop" would be my simple answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LearnSQL SSC-Addicted Points: 407 More actions · Answer 5

Sure I will correct from next by typing the content here and code in code area.

Hi Jeff -I am using third party component which will connect to another third part portal which has its own language to update the values. So i have to use SSIS and connect that third part portal and update the records as per their own language. Currently when i do with the manual process of updating the variable it gets updated, where as when passed as variable it also gets updated by there are few other records i need to update.

If i say in SQL language i.e there are set of few records are there in my table which i need to update them one by one with the help of foreach loop and execute SQL task something like

Update U set col10=col10+@SSISvariable From mytable U where U.col1='xyz' and col2='abc'

So here the @SSISvariable is dynamic which needs to get value from foreach loop.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Update U set col10=col10+@SSISvariable From mytable U where U.col1='xyz' and col2='abc'

This will update col10 in the same set of rows for each iteration of the loop. Why would you do that?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 7

LearnSQL wrote:

Sure I will correct from next by typing the content here and code in code area.
Hi Jeff -I am using third party component which will connect to another third part portal which has its own language to update the values. So i have to use SSIS and connect that third part portal and update the records as per their own language. Currently when i do with the manual process of updating the variable it gets updated, where as when passed as variable it also gets updated by there are few other records i need to update.
If i say in SQL language i.e there are set of few records are there in my table which i need to update them one by one with the help of foreach loop and execute SQL task something like
Update U set col10=col10+@SSISvariable From mytable U where U.col1='xyz' and col2='abc'
So here the @SSISvariable is dynamic which needs to get value from foreach loop.

I guess we'd need to see where/how you're getting the content of the @SSISvariable from to be able to help here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)