Upsert Dimension Table

  • Comments posted here are about the content posted at temp

  • Is it just me, or is the article missing?

    Signature is NULL

  • Hi,

    I urgently need to know how to carry out an UPSERT in SSIS 2005. If anyone knows the way to do it, please let me know as soon as possible.



  • The article is printed in one of the fullstops via microfilm (lol)

    Normal chaos will be resumed as soon as possible. :crazy:

  • Ok.... i havent the slightest idea what that meant!

  • same player shoot again .... hit ball when lights are on



    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Oh, upsert...thought this was the upskirt discussion.

    But seriously, what's wrong with the following?

    Update e

    set ColumnName = i.ColumnName

    from ImportTable i

    JOIN TableName e on i.uniqueID = e.uniqueID

    Insert TableName (ColumnName)

    select i.ColumnName

    from ImportTable i

    LEFT JOIN TableName e on i.uniqueID = e.uniqueID

    where e.uniqueID is null

    Database 101, but is there some newfangled way to do this in the new Analytics? You'd still need to build the dimension and fact tables using SQL (or something), right?

    Signature is NULL

  • Delete from the target table where the same data exists in the source table.  At this point if the source row = target row its ok.  If source row has updated values you dont care either becuase the next step is to insert all source rows to the target which is essentially overwritting rows that have been updated within the source.

    The easiest way of all however would be to use some kind of auditing within you loads from source to target.  But we dont live in a perfect world. 

    If I could VPN to a client site I would add a sample package but again, we dont live in a perfect world!!!!!

  • My question was not how do it in the DB directly. Im trying to figure out if there is a way in Sql Server Integration Services (DTS) to do the same using a transformation.

  • Hi, use Lookup transformation on SSIS data flow page with the referential table. It is possible to update in standard output flow or insert in configured error flow.


  • SSIS is memory hungry so is you use the LookUp trans will you will need to be mindful of how SSIS will chew all your memory.  I've used the process I outlined above on up to 10 million rows (which is not a lot) but SSIS manages this very well and very fast.

    Simply build your package using Execute SQL taks between dataflows to faciliate my process.

  • Oh cool! I should have thought of that before... thanks a lot :o)

  • I'm not handling such large data. But will be mindful of that... thanks for the warning.

  • Steve,

    I think one of your indexes is slightly sick. We already know all about the sale. Now, we need the Upsert article, please.

    (We all hope this is NOT a sign of things to come)



  • OK, OK, apologies. That was the weirdest thing. The process we use to load the articles is generating an old one on disk for some reason. Did it again this morning and I didn't catch it last night.

    It's up there now.

Viewing 15 posts - 1 through 15 (of 21 total)

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