December 15, 2011 at 11:07 am
Is it possible to use a populated "object" type package variable in a SET BASED update to a SQL table?
I came headlong into this architecture fully expecting to be able to do so, but all the threads I see on SSC mention "looping". I'm working with over 400 million records, so that's not an opsh.
I'd like to do something akin to this:
UPDATE <mytable> SET
somecol = someval
FROM
? as zed
INNER JOIN
<anothertable> ON <somejoinclauseinvolvingmytable>
WHERE
<mytable>.[id] = zed.[id] -- it's basically a "limiter" table.
... where ? is the object variable I populated earlier in the control flow.
Is this possible, or do recordsets basically imply RBAR execution?
Thanks. SSC is the sh@#.
-------------------------------------
also, how do I edit my weak-a$$ topic title?
December 15, 2011 at 11:33 am
You have 400-million tables, or 400-million values that go into a single table? Or something in between?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 11:43 am
400MM rows
December 15, 2011 at 11:47 am
You're going to want to break that up into smaller transactions no matter how you deal with it, or your transaction log is going to explode.
What form is the data in? Is it row/column data, XML data, a binary feed of some sort, something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 11:50 am
And you have an array in the Object variable of tables?
why not use a foreach loop? The set-based operation would still be the update performed on each table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 15, 2011 at 11:50 am
Nah, the 400MM will digest nicely if it's done set-based. I won't loop through it. The recordset in question has only a single column of integers; [id]'s of the particular rows that I want to execute the set-based UPDATE on. I'm just hoping to do so in SSIS.
Have I developed myself into a corner? I've already started formulating a workaround approach.
December 15, 2011 at 11:54 am
Then your ? is in the wrong place. The ? location you have is the table and not a value in the table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 15, 2011 at 11:56 am
How are you populating that object variable?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 15, 2011 at 12:07 pm
I'm *trying* to use it as a table in that statement. It's purpose it to simply limit the rows affected in the UPDATE. It's getting populated inside a data flow. (see attached).
December 15, 2011 at 12:08 pm
Roughly how many integers are going to be held in the variable?
If you persist with this approach, then one way or another you're going to find yourself looping. But maybe you could do the looping in a script task to build yourself a nice "(1,2,3,etc)" string - it's RBAR, but a fast version. Put that in a variable and then use that in your execute SQL task instead.
But if I were you I'd be rearchitecting the package to avoid all this.
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
December 15, 2011 at 12:14 pm
It won't work as a table in that way.
However, you can read the values from that object into a script component and create a "table" that way. Then use that in the flow.
But, how are you determining what needs to be put into that object variable? Is it from another Execute SQL Task upstream?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 15, 2011 at 12:15 pm
Yeah, agreed. Didn't know if I was missing something obvious. I'm going to create an actual table to this end and simplify my life. Moden would be proud.
December 15, 2011 at 12:20 pm
It won't work as a table in that way.
response marked as answer. Much obliged fellas.
December 15, 2011 at 12:24 pm
Greg J (12/15/2011)
Yeah, agreed. Didn't know if I was missing something obvious. I'm going to create an actual table to this end and simplify my life. Moden would be proud.
Can't blame you for the new route 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply