Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
SQL MERGE/UPSERT using the Sequel gem
# 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