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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Read more here: https://blog.mikebourgeous.com/2016/12/14/mssql-merge-upsert-with-ruby-and-the-sequel-gem/