Skip to content

Instantly share code, notes, and snippets.

Embed
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
@mike-bourgeous

This comment has been minimized.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.