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).
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
We usually add a condition on the WHEN MATCHED clause to verify that at least one of the target columns doesn’t match the corresponding source column. But, maybe doing that right requires more knowledge about which columns matter or how to compare nullible columns than it’s worth putting into a tool like this.
Good job. It’s definitely a valuable first step towards generating the statement that meets people’s conventions.
Comment by Gil — May 30, 2012 @ 1:27 pm
Good point Gil. And you’ve got exactly the right idea. If you need a little more than what’s provided in this form, than a starting point or a “first step” is exactly the way to use it.
Comment by Michael J. Swart — May 30, 2012 @ 1:31 pm
[…] MERGE Statement Generator – Making your life easier, just because he can it’s Michael J. Swart (Blog|Twitter). […]
Pingback by Something for the Weekend - SQL Server Links 01/06/12 — June 1, 2012 @ 5:05 am
The merege statement saves so much time when combined with table valued parameters. You might want to look into T4 templates. T4 templatates have enable me to not have to write any basic crud statements. This technique will save time, increase code quality, and ensure a consistent schema based approach. The current iteration of my approach incorporates if exists, the creation of a stub, and an alter statement to replace the body of the stub. You are on the rifght track with this one.
Comment by emmett childress — June 2, 2012 @ 2:59 pm
You’re right of course Emmett. It seems like the MERGE statement and Table Valued Parameters are a match made in heaven.
I’ll definitely check out T4. A quick internet search tells me that it’s probably something that I wish I knew about years ago.
Thanks Emmett
Comment by Michael J. Swart — June 3, 2012 @ 11:26 am
[…] MERGE Statement Generator | Michael J. Swart […]
Pingback by SQLQuill – Link Round Up – June 2012 Edition « SQL Feather and Quill — June 21, 2012 @ 9:53 am