CSVから PostgreSQL Upsert SQL を生成するVBAコード

Upsert SQL を生成するVBA

• PostgreSQL version 9.5 から Upsert が使える
  • 参考 : http://www.oss-db.jp/measures/dojo_21.shtml
• Upsert = INSERT して キーがかぶったら UPDATE （便利！）
• 文法は INSERT ~ ON CONFLICT ~ DO UPDATE
• でも update 対象の列はすべて指定しないといけない（めんどう）

ということで、投入するCSVの文字列からUPSERTするSQLを生成しちゃおうという話。
※CSVから取り込んだり、PostgreSQLへ接続してSQL発行するのは別に用意が必要ですのであしからず。

具体例

こんな感じの配列から・・・

user_name	user_email	user_address
test_user	[e-mail]	Tokyo

テーブル
- テーブル名: sample_table
- キー : sample_pkey
に投入するためにこんな感じのSQLをつくる。

sample.sql

     INSERT INTO sample_table(
      user_name, user_email, user_address
     )
     VALUES (
      test_user, [e-mail], Tokyo
     )
     ON CONFLICT ON CONSTRAINT sample_pkey DO UPDATE
     SET  user_name = EXCLUDED.user_name
      ,  user_email = EXCLUDED. user_email
      ,  user_address = EXCLUDED. user_address

使い方

sample.bas

Sub test___BuildUpsertSql()

  Dim res As String
  res = BuildUpsertSql("sample_table", _
                                    "sample_pkey", _
                                    "user_name, user_email, user_address", _
                                    "test_user, [e-mail], Tokyo")
End Sub

コード本体

upsert.bas

'#-----------------------------------------------------------------------------
'# Build  INSERT OR UPDATE SQL  for PostgreSQL
'#-----------------------------------------------------------------------------
Function BuildUpsertSql(sTable As String, _
                                      sTableKey As String, _
                                      sHeaders As String, _
                                      sValues As String) As String

  Dim aHeaders() As String
  Dim sUpdValues As String

  aHeaders = Split(sHeaders, ",")
  sUpdValues = ""

  '# head: | a | b | c | d |
  '# =>    a = EXCLUDED.a , b = EXCLUDED.b , c = EXCLUDED.c , d = EXCLUDED.d
  Dim h As Long
  For h = LBound(aHeaders) To UBound(aHeaders)
    If h <> LBound(aHeaders) Then sUpdValues = sUpdValues & "  ,"
    sUpdValues = sUpdValues & " " & aHeaders(h) & " = EXCLUDED." & aHeaders(h) & vbCrLf
  Next

  Dim sSql As String
  sSql = " INSERT INTO $_TABLE_$( " & vbCrLf
  sSql = sSql & "  $_HEADERS_$ " & vbCrLf
  sSql = sSql & " )" & vbCrLf
  sSql = sSql & " VALUES (" & vbCrLf
  sSql = sSql & "  $_VALUES_$ " & vbCrLf
  sSql = sSql & " )" & vbCrLf
  sSql = sSql & " ON CONFLICT ON CONSTRAINT $_KEY_$ DO UPDATE " & vbCrLf
  sSql = sSql & " SET " & sUpdValues

  sSql = Replace(sSql, "$_TABLE_$", sTable)
  sSql = Replace(sSql, "$_HEADERS_$", sHeaders)
  sSql = Replace(sSql, "$_VALUES_$", sValues)
  sSql = Replace(sSql, "$_KEY_$", sTableKey)

  BuildUpsertSql = sSql
End Function