データの挿入：バルクインサート

公開日：2019-11-28　最終更新日：2023-05-14

データの挿入：バルクインサート

エクセルVBAでデータベースを扱うためのSQL入門です。
前回でデータをINSERTすることができるようになりましたが、処理時間に不満がありました。

せっかくデータベースを使うのでしたら、エクセルで扱いに困ってしまうような大量データをストレスなく扱いたいところです。
今回は大量データをINSERTしてもストレスなく使えるようにVBAを改修していきます。

テスト用のテーブル作成

前回のデータの挿入（INSERT）と全削除で作成したワークシートおよびテーブルを使用します。

・テスト用のテーブル作成・データ挿入：INSERT INTO ・テストデータをシートで作成・INSERT INTOのサンプルVBA ・より速くINSERT INTOを処理するために・テーブルの全削除・データを挿入の最後に

テーブル定義とワークシートは以下になります。

CREATE TABLE t_sales (
 'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,'code' TEXT NOT NULL
,'name' TEXT NOT NULL
,'address' TEXT
,'sales_date' TEXT
,'item_code' TEXT
,'item_name' TEXT
,'item_price' INTEGER
,'item_count' INTEGER
,'item_amount' INTEGER
,'comment' TEXT
);

データ件数は必要に応じて行コピーしました。
最終的には100万行まで作成しましたが、さすがにパソコンが悲鳴を上げていました。
ファイルサイズも100MBを超えてしまいます。
それでも、一度作成してしまえば、なんとかエクセルでも扱えます。

VBAの改善点の概要

VBAでワークシートからINSERTしている処理を単純化すると、
・シートの1行分のデータからSQL文字列作成
・SQL発行→SQLiteでSQL実行

前回の実行時間は、10,000件で44秒でした。
そこで、上記処理のそれぞれの時間割合がどうなっているかです。
SQL発行をせずにSQL文字列だけ作成した場合は、1秒程度で終わります。

つまり、INSERTに時間がかかっています。
これを改善する最も有効な方法としてバルクインサートがあります。
複数のINSERT文をまとめて一気に実行します。

バルクインサートについては、DBごとにサポートされる内容が違ってきます。
専用のコマンドや、CSVからロードしたり、いろいろいなものがあります。
SQLiteでも、CSVからIMPORTするコマンドが用意されています。
今回は、割と多くのDBでサポートされている、複数のINSERTをまとめて実行するSQLの書き方になります。

バルクインサートのSQL解説

前回紹介した通常のiNSERTは、
INSERT INTO table_name [ column1 , column2 , ・・・ ] VALUES (value1 , value2 , ・・・ )

このSQLをデータ件数分発行することになります。
ちなみにADOでは、;セミコロンでつなげた複数行のINSERT文を発行することはできません。
仮にできたとしても、実際のSQL実行は1行ずつなので速度はあまり変わりません。
複数のINSERTをまとめるには、専用の構文があります。

INSERT INTO table_name [ column1 , column2 , ・・・ ] VALUES
(value1 , value2 , ・・・ ), (value1 , value2 , ・・・ ), (value1 , value2 , ・・・ ),・・・

VALUESの後の()ごとに1行ずつのデータをいれ、()をカンマでつなげていきます。
このSQL発行で、複数のデータを一気に追加できます。

このSQLについては、DBや環境により制限があります。
・一度にまとめられるデータ件数
・SQL文の文字列長
SQLiteの最新版では、それぞれの制限値について確認が取れませんでした。
WEBを調べてみると、500件だったが解除されたとか、100万バイトまでとか出ていますが、
今回のSQL入門用にダウンロードした最新のSQLiteでは、どちらの制限も超えています。
したがって、極端なことをしないのであれば、制限値はあまり気にする必要がないようです。
ちなみに、VBAのString型は20億文字なので全く気にする必要はありません。

バルクインサートのVBAコードと動作検証

バルクインサートに対応したVBAになります。
クラスについては前回作成した「クラスモジュール：「clsSQLite」の全VBAコード」こちらを使います。

SQL入門の「データの挿入（INSERT）と全削除」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コードクラスモジュール：clsSQLite


  
    
      Sub BulkInsert()

　　Dim sTime As Double: sTime = Timer

　　

　　Dim clsDB As New clsSQLite

　　clsDB.DataBase = "C:\SQLite3\sample.db"

　　

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　Dim maxRow As Long

　　maxRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

      　　

　　Dim sSql As String

　　Dim sSqlH As String, sSqlC As String, sSqlV As String

　　Dim i As Long, cnt As Long

　　sSqlH = "INSERT INTO t_sales "

      　　sSqlC = getValues(ws, 1, """") 'ダブルクオートで囲む

　　sSqlV = ""

　　cnt = 0

　　clsDB.DbOpen

　　

　　For i = 2 To maxRow

　　　　If sSqlV <> "" Then sSqlV = sSqlV & ","

　　　　sSqlV = sSqlV & getValues(ws, i)

　　　　cnt = cnt + 1

      　　　　If cnt = 100 Or i >= maxRow Then

　　　　　　sSql = sSqlH & sSqlC & " VALUES " & sSqlV

      　　　　　　If Not clsDB.ExecuteNonQuery(sSql) Then

　　　　　　　　MsgBox clsDB.ErrMsg

　　　　　　　　Exit Sub

　　　　　　End If

　　　　　　sSqlV = ""

　　　　　　cnt = 0

　　　　End If

　　Next

　　

　　clsDB.DbClose

　　Set clsDB = Nothing

　　Debug.Print Timer - sTime

      End Sub


      '指定シートの指定行のセル値からSQLの(value,…)を作成

Function getValues(ByVal ws As Worksheet, _

　　　　　　　　　 ByVal i As Long, _

　　　　　　　　　 Optional ByVal aQuote As String = "'") As String

　　Dim sSql As String, j As Long

　　With ws

　　　　For j = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column

　　　　　　If LenB(sSql) > 0 Then sSql = sSql & ","

　　　　　　'セルのValueのデータ型を自動判定

　　　　　　Select Case TypeName(.Cells(i, j).Value)

　　　　　　　　Case "Date"

　　　　　　　　　　sSql = sSql & dateFormat(.Cells(i, j).Value)

　　　　　　　　Case "Double"

　　　　　　　　　　sSql = sSql & .Cells(i, j).Value

　　　　　　　　Case Else

　　　　　　　　　　sSql = sSql & addQuote(.Cells(i, j).Value, aQuote)

　　　　　　End Select

　　　　Next

　　End With

　　getValues = "(" & sSql & ")"

End Function


'SQLiteは日付型がないので文字列として格納

Function dateFormat(ByVal var As Variant) As String

　　dateFormat = addQuote(Format(var, "yyyy-mm-dd"))

End Function


'シングルクオーテーションをエスケープ処理

Function addQuote(ByVal str As String, _

　　　　　　　　　Optional ByVal aQuote As String = "'") As String

　　addQuote = aQuote & Replace(str, "'", "''") & aQuote

End Function

※100件ごとにまとめたSQL文を作成しています。

10,000件で実行した結果は、
約4.8秒
もう感動ものです、劇的に速くなっています。

しかし、まだまだ1万件程度で喜んでいてはいけません。
100万件くらい処理したいところです。
では、とりあえず10万件でやってみましょう。
100,000件で実行した結果は、
約16.1秒
件数が多くなったので当然その分の時間がかかってますが、十分に速そうに思われます。
でも、いきなり100万件やるのは躊躇する程度には時間がかかっています。

もっと早くならないものか・・・
まとめる件数を100件ではなく、1,000件とか10,000件とかまとめたらどうだろう・・・
以下、何通りかの条件で実行した結果の一覧です。

処理件数	バルク数	秒数
10,000	100	4.8
100,000	100	16.1
100,000	1,000	13.5
100,000	10,000	52.1

※時間は目安です。
※テーブルの全データ削除後に実行しています。

納得いかないですよね。
バルク数を1,000件にしても大して変わらないし、10,000件にしたら・・・
おかしい、何かがおかしいです。
どこに時間がかかっているのでしょうか？
SQL発行する部分（clsDB.ExecuteNonQuery）をコメントアウトして、
10万件でバルク数1万で実行してみると、
約51.7秒
SQL発行していないので、SQL文字列作成しているだけです。
時間差は目安にしかなりませんが、
間違いなく、文字列作成にほとんどの時間を費やしているという事です。

これは、SQLiteの問題ではなく、VBAの問題だという事です。

文字列操作を改善する方法とVBAコード

VBAの文字列操作、つまり、Stringデータ型の処理はとても遅いのです。

空文字列の扱い方と処理速度について（""とvbNullString）

・String型変数のメモリ配置と取得する関数・空文字列について・String変数に空文字列を入れる・セルに空文字列を入れる・Stringが空文字列か判定・InputBox関数の戻り値が空文字列判定・空文字列の処理方法による速度比較

遅い文字列結合を最速処理する方法について

VBAは遅い… よく聞くことですが、確かに普通にコード記述しているととても遅いことがあります、その代表の一つに、文字列結合があります、文字列結合を最速処理する方法について解説します。そもそも文字列結合は、なぜ遅いのか、String型（可変長文字列）についての基礎知識が必要です。

後者のページで紹介している、Midステートメントを使用したStringbuilderクラスを少し改修して使います。
また、さらにシートへのアクセス改善に配列を使用するようにしました。
セル範囲⇔配列（マクロVBA高速化必須テクニック）｜VBA入門

・セル範囲⇔配列の基本VBA ・使用例・配列およびマクロVBAの高速化に関するページ

ただし、PC環境によっては大きなセル範囲を配列に入れる時点でエラーになる可能性があります。

以下は、これらを組み込んだVBAの全コードになります。
ADOの部分については、クラスモジュール：「clsSQLite」の全VBAコード、こちらをそのまま使っています。

SQL入門の「データの挿入（INSERT）と全削除」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コードクラスモジュール：clsSQLite

さらに、汎用的に使えるように引数で情報（DB、セル範囲、バルク数）を渡して使えるようにしています。

クラスモジュール：clsStringBuilder


  
    
      Option Explicit


Private sBuf As String

Private iBuf As Long


'現在の文字列長

Public Property Get Length() As Long

　　Length = iBuf

End Property


'初期化

Private Sub Class_Initialize()

　　sBuf = VBA.String$(32768, vbNullChar)

　　iBuf = 0

End Sub


'文字列追加

Public Function Append(ByRef sValue As String) As clsStringBuilder

　　If iBuf + Len(sValue) > Len(sBuf) Then

　　　　sBuf = sBuf & VBA.String$(VBA.Len(sBuf) * 2 + VBA.Len(sValue), vbNullChar)

　　End If

　　Mid(sBuf, iBuf + 1) = sValue

　　iBuf = iBuf + VBA.Len(sValue)

　　Set Append = Me

End Function


'文字列クリア

Public Function Clear() As clsStringBuilder

　　Call Class_Initialize

　　Set Clear = Me

End Function


'文字列化

Public Function ToString() As String

　　ToString = VBA.Left$(sBuf, iBuf)

      End Function

Stringbuilderを使ったバルクインサート対応のVBA


  
    
      Sub BulkInsertSbAry(ByVal sDb As String, _

　　　　　　　　　　ByVal sTable As String, _

　　　　　　　　　　ByVal argRange As Range, _

　　　　　　　　　　Optional ByVal bulkCnt As Long = 10000)

　　Dim clsDB As New clsSQLite

　　clsDB.DataBase = sDb

　　Dim ary

　　ary = argRange.Value

　　

　　Dim sbSql As New clsStringBuilder

　　Dim sSqlC As String

　　Dim i As Long, cnt As Long

　　

　　'カラム名取得：(column,…)

      　　sSqlC = getValues(ary, LBound(ary, 1), """")

　　

　　'DB接続

　　clsDB.DbOpen

　　

　　For i = LBound(ary, 1) + 1 To UBound(ary, 1)

　　　　If sbSql.Length = 0 Then

      　　　　　　Call sbSql.Append("INSERT INTO " & sTable)

　　　　　　Call sbSql.Append(sSqlC)

　　　　　　Call sbSql.Append(" VALUES ")

　　　　Else

　　　　　　sbSql.Append (",")

　　　　End If

　　　　

　　　　'(value,…)を作成

　　　　Call sbSql.Append(getValues(ary, i))

　　　　

　　　　'指定件数のINSERTをまとめて実行

　　　　If (i - 1) Mod bulkCnt = 0 Or i = UBound(ary, 1) Then

　　　　　　If Not clsDB.ExecuteNonQuery(sbSql.ToString) Then

　　　　　　　　MsgBox clsDB.ErrMsg

　　　　　　　　Exit Sub

　　　　　　End If

　　　　　　sbSql.Clear

　　　　End If

　　Next

　　

　　'DB切断

　　clsDB.DbClose

　　

      　　'解放

　　Set clsDB = Nothing

　　Set sbSql = Nothing

End Sub


'指定シートの指定行のセル値からSQLの(value,…)を作成

      Function getValues(ByRef ary, _

　　　　　　　　　 ByVal i As Long, _

　　　　　　　　　 Optional ByVal aQuote As String = "'") As String

　　Dim sSql As String, j As Long

　　For j = LBound(ary, 2) To UBound(ary, 2)

　　　　If LenB(sSql) > 0 Then sSql = sSql & ","

　　　　'セルのValueのデータ型を自動判定

　　　　Select Case TypeName(ary(i, j))

　　　　　　Case "Date"

　　　　　　　　sSql = sSql & dateFormat(ary(i, j))

　　　　　　Case "Double"

　　　　　　　　sSql = sSql & ary(i, j)

　　　　　　Case Else

　　　　　　　　sSql = sSql & addQuote(ary(i, j), aQuote)

　　　　End Select

　　Next

　　getValues = "(" & sSql & ")"

End Function


'SQLiteは日付型がないので文字列として格納

Function dateFormat(ByVal var As Variant) As String

　　dateFormat = addQuote(Format(var, "yyyy-mm-dd"))

End Function


'シングルクオーテーションをエスケープ処理

Function addQuote(ByVal str As String, _

　　　　　　　　　Optional ByVal aQuote As String = "'") As String

　　addQuote = aQuote & Replace(str, "'", "''") & aQuote

End Function

標準モジュールでの使い方と、100万行の実行結果

上で紹介したVBAの標準モジュールでの使い方です。


  
    
      Sub main()

　　Dim sTime As Double: sTime = Timer

　　

　　Dim sDb As String

　　sDb = "C:\SQLite3\sample.db"

　　Dim rng As Range

      　　Set rng = ActiveSheet.Range("A1").CurrentRegion

　　

      　　Call BulkInsertSbAry(sDb, "t_sales", rng, 10000)

　　

　　Debug.Print Timer - sTime

End Sub

このVBAを、10万件で実行した結果は、
約2.6秒
どうでしょう、これなら問題ないでしょう。

ということで100万件に挑戦しました。
約25秒

これなら、もう十分な速度ではないでしょうか。
SQL発行を止めて計測すると15.5秒くらいなので、
これ以上無理に速度アップさせても何秒も短縮されず効果は少ないでしょう。

100万件×10列で25秒

バルクインサートの最後に

2回に渡ってVBAからデータ追加するINSERTを解説しました。
100万件×10列で25秒という結果も出ましたので、これで十分でしょう。
といいますか、エクセルでは行数をこれ以上増やせませんし、
これ以上列数増やしたらファイルが大きくなりすぎて、エクセルでは扱うのが厳しくなってしまいます。
もし、行数は数万件で、列数が数千件というような表の場合は、
Function getValues
この中をStringbuilderで改修すると速くなるはずです。
文字列長が短かったり結合回数が少ない場合、
Stringbuilderクラスを使うとかえって時間がかかってしまいますので、今回は列処理は通常の&結合のままにしてあります。

いよいよ次回から、SQLの核心、SELECT文に入っていきます。

同じテーマ「SQL入門」の記事

テーブル名変更と列追加（ALTER TABLE）とテーブル自動作成
 データの挿入（INSERT）と全削除
 VBAクラスの全コード：データの挿入
データの挿入：バルクインサート
データの取得：条件指定（SELECT,WHERE）
VBAクラスの全コード：データの取得
 データの取得：集約集計、並べ替え（DISTINCT,GROUP,HAVING,ORDER）
SQL関数と演算子
 データベースにおけるNULLの扱い方
 データベースの正規化とマスタの作成
 全テーブル定義とテーブル自動作成VBA

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門

ホーム
その他
SQL入門
データの挿入：バルクインサート

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

SQL入門
データの挿入：バルクインサート