csv-to-values.rb

#!/usr/bin/env ruby

# Read CSV data from STDIN, output a WITH clause containing the CSV data

require 'csv'

csv = CSV.parse($stdin, headers: true, converters: :numeric, header_converters: :symbol)
columns = csv.headers.join(",")
cte_name = ENV.fetch("CTE_NAME", 'temp')

puts "WITH #{cte_name} (#{columns}) AS (VALUES " +
csv.map { |row|
  row.fields.map { |f|
    case f
    when Numeric
      f
    else
      "'#{f}'"
    end
  }.join(",")
}.join(",") + ")"

# WITH temp (k,v) AS (VALUES (0,-9999), (1, 100))