Post reply

Dynamic SQL Merge

  • April 7, 2009 at 12:03 am

    #207987

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

  • April 7, 2009 at 5:53 am

    #973236

    What about using of tablediff utility?

  • April 7, 2009 at 9:34 am

    #973520

    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/

  • April 7, 2009 at 9:39 am

    #973528

    Nice Glen 🙂

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

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

    Tommy

    Follow @sqlscribe

  • April 8, 2009 at 12:56 am

    #974035

    Thank you so much for posting this!

    🙂

  • March 15, 2013 at 7:43 am

    #1597494

    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.

  • March 15, 2013 at 8:15 am

    #1597516

    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:)

  • December 2, 2013 at 12:37 pm

    #1670208

    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!

  • December 3, 2013 at 5:57 pm

    #1670572

    Good thought there. I appreciate your comments.

  • July 23, 2018 at 4:05 pm

    #1998699

    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.

  • May 14, 2020 at 8:34 am

    #3751441

    Thank you, Glen, this is a great and useful code!

    I have question, when the link server and local server are in different Collation, then will get into error.

    Any suggestion ?

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

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