Man vs. Machine: Making life more livable through technology
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.