Merge, Metadata and the Data Mart ETL

  • Comments posted to this topic are about the item Merge, Metadata and the Data Mart ETL

  • There's a MERGE statement in SQL Server 2005? 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I don't think MERGE statement is available in SQL Server 2005. Hope it should read SQL Server 2008.

  • Krtyknm (12/27/2012)


    I don't think MERGE statement is available in SQL Server 2005. Hope it should read SQL Server 2008.

    It isn't. Hence the smiley 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hello,

    With SQL code, you use database ressource instead of server ressource it's not really "clean"

  • A minor typo (?) - Your create table has your dreaded underscore (Object_ID), but your primary key doesn't (ObjectID).

  • Yeah that's a potential negative of the method. But I find significant performance benefits with all of it happening in the database as only rows that have changed need to be touched which in some cases outweighs the need to be 'clean'. Other methods require all data be moved or complex incremental logic created or SSIS used and there's pros and cons to each. This is just a way to do it all in SQL.

  • My mistake, SQL 2005 should be 2008. Thanks for correcting! Also, I use Object_ID only to match the SQL system tables, in all other places I don't use _'s because they're annoying 🙂

    Thanks for the feedback

  • Use:

    WHEN MATCHED AND '+REPLACE(REPLACE(C.UpdateColumns,'=',''),',',' OR ')+'

    Result:

    Tgt.[DateID]Src.[DateID] OR Tgt.[ProductID]Src.[ProductID] OR ...

    I think here the sign '<>' is omitted?

    h.e. Tgt.[DateID]<>Src.[DateID] OR Tgt.[ProductID]<>Src.[ProductID] OR ...

  • @Koen Verbeeck

    Yeah!! Got it;-)

  • Is the code for the view in the "// Join Criteria" section of this article all messed up, or is it just me? There seem to be lots of strange angled quotes, which presumably should all be normal quotes. There are single long hyphens instead of two normal hyphens to indicate comments. The three REPLACE functions seem to be wrong, and don't even have enough parameters supplied, maybe they should be something like this:

    [font="Courier New"],JoinColumns = REPLACE(LEFT(C.JoinColumns, Len(C.JoinColumns) - 4), '<Object_ID>' + cast(C.object_id AS VARCHAR(16)) + '</Object_ID>', '')[/font]

    or maybe just:

    [font="Courier New"], JoinColumns = Left(C.JoinColumns,Len(C.JoinColumns)-4)[/font]

    if we remove [font="Courier New"]C2.Object_ID[/font] from the three selects near the bottom of the code?

    Can somebody correct the original article so it works just by copying and pasting it? I'm not confident about how it ought to work so I'm not sure if I'm fixing it correctly. Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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