データの挿入（INSERT）と全削除

公開日：2019-11-26　最終更新日：2021-04-28

データの挿入（INSERT）と全削除

エクセルVBAでデータベースを扱うためのSQL入門です。
前回までにテーブルを自由に作れるようになり、ワークシートからの自動作成も出来上がりました。
今回は作成したテーブルにデータを追加(INSERT)していきます。

SQLの処理を大別すると、
SELECT：抽出
INSERT：挿入
UPDATE：更新
DELETE：削除
この4つになります。
データがない事には始まりませんので、まずはINSERTでデータの挿入から解説していきます。

テスト用のテーブル作成

前回のテーブル名変更と列追加（ALTER TABLE）とテーブル自動作成で作成したVBAを使ってテーブルを作成します。

・テーブル名変更：ALTER TABLE RENAME TO ・カラム（列）追加：ALTER TABLE ADD COLUMN ・SQLの半角空白と改行とセミコロン・テーブル自動作成・テーブル名変更と列追加とテーブル自動作成の最後に

SQLは以下になります。

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
);

データ型やNOT NULLは特段の意味はありませんので、適宜変更しても問題ありません。

データ挿入：INSERT INTO

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

※INTOはDBによっては省略できます。

カラム指定を省略することで全カラムのデータが追加できますが、
その場合は、valueはテーブル定義のカラム順通りに記述する必要があります。
ですが、カラム省略はRDB（リレーショナルデータベース）の良さが生かされなくなってしまいます。
機能拡張でカラム追加したり、テーブル再構築時に列が入れ替わってしまうとエラーとなってしまいます。

カラムを指定した場合は、カラム順は任意の順番で記述できます。
columnとvalueのそれぞれの指定位置は一致させる必要があります。
指定を省略したカラムは、デフォルト値が設定されていればその値、デフォルト値がなければNULLになります。

INSERT INTO t_sales 
      ('code','name','address','sales_date')
      VALUES 
      ('001','販売先001','住所001','2019-11-26')

idは自動インクリメントなので省略することで自動連番になります。
VALUESの文字列は、シングルクオーテーションで囲みます。
数値はそのまま指定できます。

INSERT INTOの使用例


  
    
      Sub InsertSql()

　　Dim clsDB As New clsSQLite

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

      　　

　　Dim sSql As String

　　sSql = ""

　　sSql = sSql & "INSERT INTO t_sales"

　　sSql = sSql & " (""code"",""name"",""address"",""sales_date"""

　　sSql = sSql & " ,""item_code"",""item_name"",""item_price"",""item_count"",""item_amount"",""comment"") "

　　sSql = sSql & " VALUES "

　　sSql = sSql & " ('062','販売先062','住所062','2019-11-30'"

　　sSql = sSql & " ,10065,'商品10065',1400,6,8400,'備考001')"

　　If Not clsDB.ExecuteNonQuery(sSql) Then

　　　　MsgBox clsDB.ErrMsg

　　　　Exit Sub

　　End If

　　

　　Set clsDB = Nothing

End Sub

テーブルの作成/削除（CREATE TABLE,DROP TABLE）

・データベースで使う用語や記号について・カラムのデータ型・テーブル作成：CREATE TABLE ・テーブル削除：DROP TABLE ・ADODB.ConnectionのExecuteメソッド・クラスを拡張修正・VBAでADOを使う為のクラスの全VBA ・テーブルの作成/削除の最後に

こちらで作成したクラスを使用しています。

テストデータをシートで作成

このようなテストデータを用意します。
手入力ではデータ作成が大変なので、筆者は関数を入れました。

A2	=TEXT(RANDBETWEEN(1,100),"000")
B2	="販売先"&A2
C2	="住所"&A2
D2	=RANDBETWEEN(DATE(2019,11,1),DATE(2019,11,30))
E2	=TEXT(RANDBETWEEN(10001,10099),"00000")
F2	="商品"&E2
G2	=ROUND(RANDBETWEEN(1100,1800),-2)
H2	=RANDBETWEEN(5,20)
I2	=G2*H2
J2	="備考"&TEXT(ROW()-1,"000")

TEXT関数とRANDBETWEEN関数だけなので、難しくないと思います。
SQLのテストに使うだけなのでデータ内容は気にする必要がないでしょう。
このようにしておけばデータ量は自在に調整できます。

とりあえず1万件用意しました。

INSERT INTOのサンプルVBA


  
    
      Sub InsertFromSheet()

      　　Dim clsDB As New clsSQLite

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

      　　
　　Dim ws As Worksheet

      　　Set ws = ActiveSheet

      　　

      　　Dim sSql As String

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

      　　Dim i As Long

      　　

      　　Dim sTime As Double: sTime = Timer

      　　sSqlH = "INSERT INTO t_sales "

      　　'カラム名はダブルクオートで囲っています。

      　　sSqlC = getValues(ws, 1, """")

      　　For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

      　　　　sSqlV = getValues(ws, i)

      　　　　sSql = sSqlH & sSqlC & " VALUES " & sSqlV

      　　　　If Not clsDB.ExecuteNonQuery(sSql) Then

      　　　　　　MsgBox clsDB.ErrMsg

      　　　　　　Exit Sub

      　　　　End If

      　　Next

      　　Debug.Print Timer - sTime

      　　

      　　Set clsDB = Nothing

      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, sTmp As String

      　　Dim j As Long

      　　With ws

      　　　　sSql = "("

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

      　　　　　　If sSql <> "(" Then sSql = sSql & ","

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

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

      　　　　　　　　Case "Date"

      　　　　　　　　　　sTmp = dateFormat(.Cells(i, j).Value)

      　　　　　　　　Case "Double"

      　　　　　　　　　　sTmp = .Cells(i, j).Value

      　　　　　　　　Case Else

      　　　　　　　　　　sTmp = addQuote(.Cells(i, j).Value, aQuote)

      　　　　　　End Select

      　　　　　　sSql = sSql & sTmp

      　　　　Next

      　　　　sSql = sSql & ")"

      　　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

VBA内のコメントに記載しましたが、
セルのValueのデータ型を自動判定し、SQL用に編集しています。
カラム名はダブルクオートで囲っています。

SQLiteでは、シングルクオーテーション、バッククオーテーション等も可
MySQLであれば`バッククォート、SQL ServerやAccessは[]角括弧で囲みます。
その他のDBでは概ねダブルクオーテーションで囲みます。

SQLiteは日付型がないのでyyyy-mm-ddとして文字列として格納しています。
SQLでは'シングルクオーテーションは特別な文字になりますのでエスケープ処理が必要です。

この実行時間は、
1万件のINSERTで約63秒でした。
実行する度に時間は若干ずれますし、PC環境によるので目安としてお考えください。

時間がかかっています、遅いですよね。
待てない時間ではないですが、10万件とかになったら、ちょっと困ります。

より速くINSERT INTOを処理するために

デーベースの接続/切断を見直す

上記のVBAでは、DBのOpenとCloseをクラスに委ねています。
クラスでは、接続が無ければ自動で接続して、そして自動で切断しています。
つまり、SQL発行のたびに接続/切断が繰り返されています。
これはあきらかに無駄です。
最初に接続しておき、SQL発行が終わったら切断したほうがより効率的です。


  
    
      　　Dim sTime As Double: sTime = Timer

      　　clsDB.DbOpen

　　sSqlH = "INSERT INTO t_sales "

      　　sSqlC = getValues(ws, 1, """")

　　For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

　　　　sSqlV = getValues(ws, i)

　　　　sSql = sSqlH & sSqlC & " VALUES " & sSqlV

　　　　If Not clsDB.ExecuteNonQuery(sSql) Then

　　　　　　MsgBox clsDB.ErrMsg

　　　　　　Exit Sub

　　　　End If

　　Next

      　　clsDB.DbClose

　　Debug.Print Timer - sTime

この実行時間は、
1万件のINSERTで約45秒でした。
明らかに速くはなっていますが、今一つな感じではあります。

ADOのCommandオブジェクトを使う

ADOにはCommandオブジェクトがあり、パラメーター方式でSQLを実行できます。
同じSQLを実行する場合は、データだけをパラメーターとして渡すのでより効率的・・・


  
    
      Sub InsertWithCommand()

      　　Dim clsDB As New clsSQLite

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

      　　

      　　Dim ws As Worksheet

      　　Set ws = ActiveSheet

      　　

      　　Dim sSql As String

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

      　　Dim i As Long

      　　
　　Dim sTime As Double: sTime = Timer

　　clsDB.DbOpen

　　sSqlH = "INSERT INTO t_sales "

      　　sSqlC = getValues(ws, 1, """")

　　sSqlV = getParams(ws, 1)

　　Call clsDB.SetCommandText(sSqlH & sSqlC & " VALUES " & sSqlV)

　　For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

　　　　If Not clsDB.ExecuteCommand(getParamValues(ws, i)) Then

　　　　　　MsgBox clsDB.ErrMsg

　　　　　　Exit Sub

　　　　End If

　　Next

　　clsDB.DbClose

　　Debug.Print Timer - sTime

      　　

      　　Set clsDB = Nothing

      End Sub


      '指定シートの見出し行からSQLの(?,…)を作成

      Function getParams(ByVal ws As Worksheet, _

      　　　　　　　　　　　　ByVal i As Long) As Variant

      　　Dim vSql As Variant

      　　Dim maxCol As Long

      　　With ws

      　　　　maxCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

      　　End With

      　　ReDim vSql(1 To maxCol)

      　　Dim j As Long

      　　For j = 1 To maxCol

      　　　　vSql(j) = "?"

      　　Next

      　　getParams = "(" & Join(vSql, ",") & ")"

      End Function


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

      Function getParamValues(ByVal ws As Worksheet, _

      　　　　　　　　　　　　ByVal i As Long) As Variant

      　　Dim vSql As Variant

      　　Dim maxCol As Long

      　　With ws

      　　　　maxCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

      　　　　'Index関数で1次元に取すので無理やり複数行にしています。

      　　　　vSql = .Range(.Cells(i, 1), .Cells(i + 1, maxCol))

      　　End With

      　　getParamValues = WorksheetFunction.Index(vSql, 1)

      End Function

基本的な処理は、先のVBAと同じです。
先のVBAに上記のVBAを追加します。

クラスにも追加しています。
追加した部分のみ掲載します。


  
    
      Private pAdoCmd As ADODB.Command

      '・・・

      Public Property Set AdoCmd(arg As ADODB.Command)

      　　Set pAdoCmd = arg

      End Property

      Public Property Get AdoCmd() As ADODB.Command

      　　Set AdoCmd = pAdoCmd

      End Property

      '・・・

      'CommandにSQLを設定

      Public Function SetCommandText(sSql As String) As Boolean

      　　On Error GoTo Err_Exit

      　　

      　　Set Me.AdoCmd = New ADODB.Command

      　　Set Me.AdoCmd.ActiveConnection = Me.AdoCon

      　　Me.AdoCmd.CommandText = sSql

      　　

      　　SetCommandText = True

      　　Call resetErr

      　　Exit Function

      　　

      Err_Exit:

      　　SetCommandText = False

      　　Call setErr(Err, "SetCommandText")

      End Function


      'SQL実行：CommandのParametersを使用

      Public Function ExecuteCommand(vParam As Variant) As Boolean

      　　On Error GoTo Err_Exit

      　　

      　　Call Me.AdoCmd.Execute(Parameters:=vParam)

      　　

      　　ExecuteCommand = True

      　　Call resetErr

      　　Exit Function

      　　

      Err_Exit:

      　　ExecuteCommand = False

      　　Call setErr(Err, "ExecuteCommand")

      End Function

※クラスモジュール：「clsSQLite」の全VBAコード

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

この実行時間は、
1万件のINSERTで約44秒でした。
計測誤差と言っても良いくらいの違いしかありませんでした。
とはいえ、何回かやってみても僅かではあるものの、こちらの方が速いことは速いようです。
ですが、実際に使うにあたってメリットがあるという程の違いがないことは明らかです。

確実に速くはなりましたが、まだまだ遅いと感じます。
数十万件になったら1時間とかかかってしまいます。
数十万以上のデータ件数でも、あまりストレスなく処理できるように出来ないものでしょうか。
次回は、これをさらに時間短縮する方法を検討していきます。

MySqlとの比較

SQLiteと同様にローカルのMySqlで同じ処理を実行してみました。
その実行時間は、
1万件のINSERTで約25秒でした。
やはり、確かにSQLiteのINSERTは遅いと感じます。

テーブルの全削除

SQLをいろいろテストしていく上で、テーブルのデータを全削除したくなります。
もちろん、テーブルを削除して再作成すれば良いのですが、別の方法も紹介しておきます。


  
    
      Sub DeleteTable()

      　　Dim clsDB As New clsSQLite

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

      　　

      　　Dim sSql As String

      　　sSql = "DELETE FROM t_sales;"

      　　If Not clsDB.ExecuteNonQuery(sSql) Then

      　　　　MsgBox clsDB.ErrMsg

      　　　　Exit Sub

      　　End If

      　　sSql = "VACUUM;"

      　　If Not clsDB.ExecuteNonQuery(sSql) Then

      　　　　MsgBox clsDB.ErrMsg

      　　　　Exit Sub

      　　End If

      　　

      　　Set clsDB = Nothing

      End Sub

DELETE FROM t_sales
これで、テーブルの全データを削除できます。

VACUUM
これはSQLite独自のコマンドになります。
DBはデーフの追加/削除を繰り返すと無駄な領域が増えていきますので、これを圧縮しています。
SQLのDELETE文の詳細については後々の章で説明します。
ここでは、テストをスムーズに行う為の手段として、単純に全削除する方法を紹介しました。

SQLite以外のDBでは

TRUNCATE TABLE tabel_name
これでテーブルの全行を高速に削除することができます。
TRUCATETABLEが使えるDBの場合は、こちらを使うことをお勧めします。

データを挿入の最後に

今回で、データをINSERTすることができるようになりました。
しかし、せっかくデータベースを使うのでしたら、エクセルで扱いに困ってしまうような大量データをストレスなく扱いたいところです。
データ量が少ないのであれば、エクセルだけで十分ですからね。
少なくとも数十万件以上のデータを楽に扱えるようになりたいところです。

ただし、今回のINSERTはデータを新規に作成しているので時間がかかっているものです。
一度データベースに入れた後にデータを取り出すのは、はるかに短い時間で済みます。
次回INSERTの処理時間の短縮が終わったら、その次はいよいよデータ抽出に進みます。

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

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

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

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入門
データの挿入（INSERT）と全削除

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

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

SQL入門
データの挿入（INSERT）と全削除