MSSQL MERGE/UPSERT with Ruby and the Sequel gem

My last post contained some code snippets useful for working with an Oracle database from Ruby. I’ve also had to interface with Microsoft’s SQL Server database (to add data to a data warehouse) using Ruby and Sequel. Here’s a snippet for using the MERGE statement with the Sequel gem, useful unless/until Sequel gets official MERGE support. This snippet only supports basic UPSERT functionality, where data is updated when the key is matched, inserted when not matched. It does support multi-column keys for matching.

The code builds the SQL dynamically since it has to insert a different number of parameters based on how many columns are being updated or used as keys.

Make sure you read the code comments from the snippet. Also make sure you’ve read up on how the Sequel gem qualifies schema, table, and column names using double- and triple-underscore.

You’ll need the following gems: