Merge, Metadata and the Data Mart ETL

Question

Merge, Metadata and the Data Mart ETL

chris.ross 34852

SSC Veteran

Points: 295
More actions
December 26, 2012 at 11:26 pm

#272388

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

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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

Krtyknm SSC Eights! Points: 884 More actions · Answer 2

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

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

benji41414 5900 Grasshopper Points: 21 More actions · Answer 4

Hello,

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

nick.kaye SSC Rookie Points: 48 More actions · Answer 5

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

chris.ross 34852 SSC Veteran Points: 295 More actions · Answer 6

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.

chris.ross 34852 SSC Veteran Points: 295 More actions · Answer 7

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

baclanov Valued Member Points: 60 More actions · Answer 8

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

Krtyknm SSC Eights! Points: 884 More actions · Answer 9

Krtyknm

SSC Eights!

Points: 884

December 27, 2012 at 11:58 pm

#1572019

@Koen Verbeeck

Yeah!! Got it;-)

gward 98556 Mr or Mrs. 500 Points: 538 More actions · Answer 10

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.