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.

# Code to perform a basic update/insert using MERGE. Tested with MSSQL # and the TinyTDS adapter. Assumes DB contains a Sequel::Database. require 'sequel' module Merge # Returns a Sequel::Dataset that will update or insert the given Array of # Hashes of +data+ into the given named +table+, with the given primary # +key+(s). Hash key names in +data+ must match the table's column names # The dataset will return one row for each row that was inserted or updated, # with the action performed in the :$action column. # # Examples: # Merge.merge( # :schema1__table1, # :col1, # [{col1: 1, col2: 2, col3: 3}, ...] # ).all # # => [{ :$action => 'UPDATE', :line_id => 1 }, ...] # # Merge.merge( # :schema2__table2, # [:col1, :col2], # [{col1: 1, col2: 2, col3: 3}, ...] # ).all # # => [{ :$action => 'INSERT', :col1 => 1, :col2 => 2 }, ...] def self.merge(table, key, data) raise 'No data given' if data.empty? || data.first.empty? # Ensure consistent column order cols = data.first.keys values = data.map{|h| cols.map{|k| h[k] } } key = [key] unless key.is_a?(Array) key_sql = (['? = ?'] * key.size).join(' AND ') key_params = key.map{|k| [Sequel.qualify(:target, k), Sequel.qualify(:source, k)] }.flatten update_cols = (cols - key) update_sql = (['? = ?'] * update_cols.size).join(', ') update_params = update_cols.map{|k| [Sequel.qualify(:target, k), Sequel.qualify(:source, k)] }.flatten sql = <<-SQL MERGE INTO ? AS ? USING (VALUES #{(['?'] * values.size).join(', ')} ) AS ? ? ON #{key_sql} WHEN MATCHED THEN UPDATE SET #{update_sql} WHEN NOT MATCHED THEN INSERT ? VALUES ? OUTPUT $action, #{(['?'] * key.size).join(', ')} ; SQL DB[ sql, table, :target, *values, :source, cols, *key_params, *update_params, cols, cols.map{|k| Sequel.qualify(:source, k) }, *key.map{|k| Sequel.qualify(:source, k) }, ] end end

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:

blog.MikeBourgeous

Making life more livable through technology

MSSQL MERGE/UPSERT with Ruby and the Sequel gem