Michael J. Swart

May 30, 2012

MERGE Statement Generator

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 12:00 pm

So what I've got here below is a web form. It takes a source query and target table and spits out a merge statement for the standard insert/update use case. I made it for myself but you might find it useful too. I'm counting on this to be a real time saver (at least for me).

Clustered Index Merge Showplan Operator

Some Neat Things

  • This webform doesn't post to any server. It's all javascript that gets executed in your browser. This means you don't have to worry about anyone stealing your code and I don't have to worry about this form going viral (ha ha).
  • SQL Snippets (either kind) don't quite handle the dynamic list of columns here. Otherwise, you would be reading a different post today.
  • The MERGE statement is a lot more versatile than what I show here. I'm just handling the most common use case.

Things to Watch For

  • Concurrency. If this is important to you, remember to use appropriate locks (usually UPDLOCK) on the target table.
  • Make sure the column list of the source query matches that of the target table
  • You're going to have to clean up the script if your target table has columns that are rowversion, identity, computed etc...
  • User input is being used to generate code here. So my SQL-injection spidey-sense starts to tingle. But it's okay in this case because I'm not running anything. I'm just displaying it. You're the one who's running this stuff so it's up to you to vouch for any generated code. Take care.

The Form

Put in your own data (or just use the sample here). When you're ready, hit MERGE!
Source Query
Target Table
Key Columns (one column per line)
Other Columns (one column per line)

Your Merge Query

Powered by WordPress