SQL MERGE/UPSERT using the Sequel gem
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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
Read more here: https://blog.mikebourgeous.com/2016/12/14/mssql-merge-upsert-with-ruby-and-the-sequel-gem/