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
- 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.
Put in your own data (or just use the sample here). When you're ready, hit MERGE!