sequel_mssql_merge.rb

## sequel_mssql_merge.rb
# 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