SQL入門
データの挿入(INSERT)と全削除

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-08

データの挿入(INSERT)と全削除


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


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

テスト用のテーブル作成

VBA マクロ SQL ADO

前回のテーブル名変更と列追加(ALTER TABLE)とテーブル自動作成で作成したVBAを使ってテーブルを作成します。
エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。今回は作成したテーブルの名称変更とカラム(列)を追加するSQLを解説します。そして、エクセルのシートにテーブル情報を記入して、そこからテーブルを自動作成するVBAも紹介します。
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 ws As Worksheet
  Set ws = ActiveSheet
  
  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)
エクセルVBAでデータベースを扱うためのSQL入門です。前回までにデータベースを作成し、ADOで接続できるようにしましたので、今回から実際にSQLを発行して、データベースを操作していきます。テーブルを作成したり削除する場合のSQLについて解説します。
こちらで作成したクラスを使用しています。

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

VBA マクロ SQL ADO

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

A2 =TEXT(RANDBETWEEN(1,100),"000")
B2 ="販売先"&B2
C2 ="住所"&B2
D2 =RANDBETWEEN(DATE(2019,11,1),DATE(2019,11,30))
E2 =TEXT(RANDBETWEEN(10001,10099),"00000")
F2 ="商品"&B6
G2 =ROUND(RANDBETWEEN(1100,1800),-2)
H2 =RANDBETWEEN(5,20)
I2 =B8*B9
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)と全削除
データの挿入:バルクインサート
データの取得:条件指定(SELECT,WHERE)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)
SQL関数と演算子
データベースにおけるNULLの扱い方
データベースの正規化とマスタの作成
テーブルを結合して取得(INNER JOIN,OUTER JOIN)


新着記事NEW ・・・新着記事一覧を見る

他のテーブルのデータで追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)
SQL関数と演算子|SQL入門(12月1日)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)|SQL入門(11月30日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • データの挿入(INSERT)と全削除

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


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



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