【Excel】自作関数でSQL(Insert文)を作成する

仕事でDBのテーブル定義書をよく作るのですが、Excelに記載しているものが多いため、Excelの関数などを使用してSQL文を作成したくなります。

以前、最も必要になりそうなCreate文を作成するVBAにつきまして、以下の記事で作成をしてみました。

Create文の次に必要になりそうなInsert文についても、あらかじめテンプレートのようなものを作成しておくのが良いかとなと思いました。

そこで他のサイトの情報などを参考にしてみましたが、Excel関数だけを使用してInsert文を作成しているものが多いです。

後述しますが、Excel関数だけを使用するパターンだとテーブルの定義が追加になると、都度メンテナンスが必要になってしまいます。

そこで、今回はVBAで自作関数を作成してみることにしました。

Insert文の元になる表を作る

まず、前提として以下のような表からInsert文を作成することにします。

Insert文を作成するにあたっては

• データを挿入するテーブル名
• カラム名
• カラムの型
• 挿入するデータ

は最低限必要となるので、少し面倒ですがインプットデータとしてExcel上の表に入力する必要があります。

自分はOracleで仕事をすることが多いので、型の種類はOracleの型であるNUMBERなどを使用しています。

他のDBを使用する場合は、読み替えていただければと思います。（例えば、MySQLの場合は数位型はINTなどになります。）

Insert文を行う自作関数(VBA)

設計

前述したとおり、テーブル名、カラム名、カラム型、挿入するデータがインプットとして必要なので、これらの4つを引数に持つ関数を作成します。

ただ、テーブルは必ず1つに決まりますが、カラムの数は複数あるので複数の値を受け取れるようにする必要があります。

そこでカラム、カラム型、挿入するデータはセルを範囲指定できるようにしたいと思います。

作成したコード

VBAで自作関数を作成します。実際に作成してみたコードが以下になります。

Public Function createInsert(tableName As String, columns As Range, columnTypes As Range, datas As Range) As String
  createInsert = "INSERT INTO " & tableName & "("

  Dim i As Integer

  'カラム名をループさせる
  For i = 1 To columns.Count
    If i > 1 Then
      createInsert = createInsert & ","
    End If
    createInsert = createInsert & columns(i).Value
  Next i

  createInsert = createInsert & ")VALUES("

  'データをループさせる
  For i = 1 To datas.Count
    If i > 1 Then
      createInsert = createInsert & ","
    End If

    If datas(i) = "NULL" Then
      'NULLの場合はクォーテーションなし
      createInsert = createInsert & "NULL"
    ElseIf datas(i) = "NOW()" Then
      'SYSDATE関数の場合はクォーテーションなし
       createInsert = createInsert & "SYSDATE"
    Else
       Select Case columnTypes(i)
         Case "CHAR", "VARCHAR2", "VARCHAR"
           '文字列型の場合はクォーテーションで囲む
           createInsert = createInsert & "'" & datas(i).Value & "'"
         Case "DATE"
           'DATE型の場合は、TO_DATE関数にYYYY-MM-DDを指定
           createInsert = createInsert & "TO_DATE('" & datas(i).Value & "', 'YYYY-MM-DD’)"
         Case "TIMESTAMP"
           'TIMESTAMP型の場合は、TO_DATE関数にYYYY-MM-DD HH24:MI:SSを指定
           createInsert = createInsert & "TO_DATE('" & datas(i).Value & "', 'YYYY-MM-DD HH24:MI:SS’)"
         Case Else
           '文字列、日付型以外はクォーテーションなし
           createInsert = createInsert & datas(i).Value
         End Select

    End If
  Next i

  createInsert = createInsert & ");"
End Function

作成におけるポイントとしては以下になります。

• 文字列型(VARCHAR2やCHAR）は、データをクォーテーションで囲む必要がある
• ただし、値がNULLの場合は文字列型でもクォーテーションでは囲まない
• 数値型はデータをクォーテーションで囲む必要がない
• 日付型は、フォーマットを指定して変換してあげる必要がある

日付型のフォーマットは、今回はDATE型ならYYYY-MM-DD、TIMESTAMP型ならYYYY-MM-DD HH24:MI:SSの形式でデータが設定されている前提としました。

別のフォーマットが良い場合は、コード内のフォーマットの型を変更します。更に汎用的に作るのであれば、optionalの引数で受け取れる形にするのが良いですかね。

また、日付にSYSDATEと入力した場合にはSYSDATE関数が使用されるようにしました。

制約としては、セルにNULLという文字があった場合にはNULLとしてInsertするため、”NULL”という文字をInsertすることはできません。（そんな値をInsertすることも、ほとんどないと思いますが…）

使い方

以下のように引数を指定して使用します。

第一引数：テーブル名

第二引数：カラム名（範囲指定）

第三引数：カラムの方（範囲指定）

第四引数：データ（範囲指定）

結果は以下のように表示されます。

妥当性チェックはしていないので、当然ですがデータがおかしければエラーになります。数値型のセルに文字が入っていたりした場合です。

「VBAで実装しているのだから、その辺はチェックできるんじゃないの？」と思われるかもしれませんが、セルから呼ばれる自作関数だとエラーの内容を表現するのが難しいです。

例えば複数エラーがあった時に、どうするのかという問題に当たります。

今だとスピルで複数を返せるかもしれませんが、横のセルに値が入っていたらアウトです。

なので、妥当性のチェックなどを求められたときは、別途VBAを組んで実施するのが良いのかなと思います。

※そもそもテーブルの一意制約エラーとか、実際にSQLを発行してみないと判明しない妥当性エラーもあるので、とりあえずやってみて駄目なら直せばいいと考えています。

関数でなくVBA自作関数を使うメリット

冒頭で少し触れた件ですが、Excel関数を使用してInsert文を作成するとカラム数の数が違うテーブルだと使いまわすことができません。

例えば以下の式でもInsert文を作成することができます。

="INSERT INTO "&B1&"("&A2&","&B2&","&C2&","&D2&","&E2&")VALUES("&A4&",'"&B4&"','"&C4&"',TO_DATE('"&D4&"', 'YYYY-MM-DD'),NOW());"

しかし、上記は1セルごとにアドレスを指定しなければいけないので、仮に列の数が1つ増えると式のメンテナンスが必要になってしまいます。

一方で今回のようにVBAで自作関数を作成しておけば、カラムが変更となっても指定範囲を変えるだけでよいので楽です。