Dynamic SQL Merge

  • Glen Schwickerath

    SSC Journeyman

    Points: 90

    Comments posted to this topic are about the item Dynamic SQL Merge

  • leon-927648

    Grasshopper

    Points: 23

    What about using of tablediff utility?

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    I think this procedure is impressively well done.

    With that said, It does seem to be a bit of a solution looking for a problem. Writing the merge statement directly is not at all challenging and it seems easier to simply do that then learn the nuances of this procedure, especially since writing it directly permits it to be made more nuanced if so desired.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Tommy Bollhofer

    SSChampion

    Points: 14940

    Nice Glen 🙂

    I just posted a similar SP - just went about a little different.....

    http://www.sqlservercentral.com/scripts/SQL+2008/66507/[/url]

  • brendan.hargie

    SSC Journeyman

    Points: 85

    Thank you so much for posting this!

    🙂

  • chris.stamand

    Grasshopper

    Points: 11

    Excellent work. Never mind the comment about this being a solution looking for a problem, this proc was exactly what I needed. We have lots of different reference data tables what I want to keep "relatively" in sync and this gives me 99% of what I need. The other 1% I can tweak the proc to make happen.

    One thing, in your example 2, merging from a linked server, I believe you need to change the @srcType value sent in to 'LINK' from 'SQL'. That's what I needed to do in order to pull from the linked server. It wouldn't work otherwise.

  • Glen Schwickerath

    SSC Journeyman

    Points: 90

    Thank you very much for your kind feedback. Your comments are correct regarding the "link". The actual usage for this was originally to an AS400 linked server.

    As so often happens when you provide a tool which makes coding easier, I've now had to re-train folks so that they don't over-use the MERGE statement:)

  • prestonjd

    Valued Member

    Points: 50

    This is a great piece of code. One change I would recommend is in step 4 using NULLIF so if the source or destination contains a null it is caught.

    Something like this:

    SELECT @TempSQL = @TempSQL + 'NULLIF(S.'+cols.SrcColumn+', T.'+cols.SrcColumn+') IS NOT NULL or '

    Thanks!

  • Glen Schwickerath

    SSC Journeyman

    Points: 90

    Good thought there. I appreciate your comments.

  • wm.thompson1

    SSC Rookie

    Points: 46

    Nice - a comment;
    I Noticed the null handling as per SSC Rookie;
    One suggestion would be something like (paraphrase)
    IF varchar then use isnull(<s.fieldname>,'') <> ditto t
    IF number then use ISNULL(s.<fieldname>, -3000) <> ditto t
    IF date then use ISNULL(s.<fieldname>, '1901-01-01') <> ditto t  -- or something along this line.

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

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