Using recordset object variables in SET BASED operations: Possible?

  • 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?

  • 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

  • 400MM rows

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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).

  • 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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

  • 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.

  • It won't work as a table in that way.

    response marked as answer. Much obliged fellas.

  • 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 14 (of 14 total)

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