Dynamic SQL Merge

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

  • What about using of tablediff utility?

  • 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/

  • Nice Glen 🙂

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

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

  • Thank you so much for posting this!

    🙂

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

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

  • 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!

  • Good thought there. I appreciate your comments.

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

  • 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 11 (of 11 total)

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