INSERT文の作成をVBAで自動化する

データパッチを当てるとき、Excelでデータを整理し、さてパッチ当てのSQLを作ろうとしたとき、あまりの量の多さにめまいがしたことはないだろうか。

このExcelの値をそのままSQL文にできたらいいのに、と誰もが思うはずである。

単純作業はコンピュータにやらせて、人間は頭を使ったほうがいい。
というわけで、SQLを生成するマクロを作ってみた。
まずは作成ロジックが簡単なINSERT文から。

以下は、INSERT文の作成を自動化するVBAのサンプルである。
コメントをつけたので、必要に応じて書き換えて使ってください。

①プロパティを設定する

②データのテーブルを記載するフォーマット

※列や行の末尾は「●」で判別するようにした。

③ ②で設定したフォーマットに入れた値をINSERT文にして吐き出すVBAサンプル。

Const Properties As String = "プロパティ"
Const INSERT_SHEET As String = "INSERT"
Option Explicit

Sub INSERT文生成()
    Dim TABLE_NAME As String
    Dim Column As Long
    Dim Row As Long
    Dim END_Column As Long
    Dim OutputFolder As String
    Dim OutputTextName As String
    Dim OutputText As String
    
    
    '--------------------
    ' プロパティの設定
    '--------------------
    TABLE_NAME = Worksheets(Properties).Range("B2").Value
    OutputFolder = Worksheets(Properties).Range("B3").Value
    OutputTextName = Worksheets(Properties).Range("B4").Value
    
    '---------------------------------
    'フォルダの末尾が[\]でなければ追記
    '---------------------------------
    If Right(OutputFolder, 1) <> "\" Then
        OutputFolder = OutputFolder & "\"
    End If
    
    OutputText = OutputFolder & OutputTextName
    
    '--------------------
    ' カラムの末尾の設定
    '--------------------
    Column = 2
    Do While Worksheets(INSERT_SHEET).Cells(2, Column) <> "●"
        Column = Column + 1
    Loop
    'カラム末尾の列番号
    END_Column = Column - 1

    Row = 4
    Open OutputText For Output As #1
    Do While Cells(Row, 1).Value <> "●"
        Column = 2
        
        Print #1, "INSERT INTO " & TABLE_NAME & "("
        For Column = 2 To END_Column
            
            '-------------------------------
            ' カラム名を出力する
            ' 最終カラム以外は[,]で区切る
            '--------------------------------
            If Column <> END_Column Then
                Print #1, "   " & Cells(2, Column).Value & ","
            Else
                Print #1, "   " & Cells(2, Column).Value
            End If
        Next
        
        Print #1, ")"
        Print #1, "VALUES("
        For Column = 2 To END_Column
            
                '--------------------------------
                ' 最終カラム以外は[,]で区切る
                '--------------------------------
                If Column <> END_Column Then
                    
                    '------------------------------
                    ' 空白の場合はNULL
                    '------------------------------
                    If Cells(Row, Column).Value = "" Then
                        Print #1, "   NULL,"
                    Else
                        '-----------------------------
                        ' シングルクォテーションの要否
                        '-----------------------------
                        If Cells(3, Column).Value = "○" Then
                            Print #1, "   '" & Cells(Row, Column).Value & "',"
                        Else
                            Print #1, "   " & Cells(Row, Column).Value & ","
                        End If
                    End If
                    
                Else
                    
                    '----------------------------
                    ' 空白の場合はNULL
                    '----------------------------
                    If Cells(Row, Column).Value = "" Then
                        Print #1, "   NULL"
                    Else
                        '-----------------------------
                        ' シングルクォテーションの要否
                        '-----------------------------
                        If Cells(3, Column).Value = "○" Then
                            Print #1, "   '" & Cells(Row, Column).Value & "'"
                        Else
                            Print #1, "   " & Cells(Row, Column).Value
                        End If
                    End If
                End If
        
        Next
        Print #1, ");"
        Row = Row + 1
    Loop
        
    Close #1

End Sub

上記のマクロを実行した結果は以下の通り。

INSERT INTO dept_copy(
   DNAME,
   DEPTNO,
   LOC
)
VALUES(
   'システム',
   70,
   '横浜'
);
INSERT INTO dept_copy(
   DNAME,
   DEPTNO,
   LOC
)
VALUES(
   '開発',
   80,
   '東京'
);
INSERT INTO dept_copy(
   DNAME,
   DEPTNO,
   LOC
)
VALUES(
   '経理',
   90,
   NULL
);
INSERT INTO dept_copy(
   DNAME,
   DEPTNO,
   LOC
)
VALUES(
   'プログラム',
   NULL,
   '福岡'
);

作成したSQLをSQLplusで実行した結果。

実際の現場では、SQL DeveloperやObject Browzerを使って、作成したSQLをまとめて実行するといいと思います。

サンプルを書くのにだいたい1時間くらいかかりますが、このブログを見てくれた人は上のコードをコピペして使えば５分で済むし(笑)、
手作業でSQLを書くよりも全然効率がよくなるはず！

次はUPDATE文を自動生成するVBAを作ります～。