Access2013テーブルからSQLServer2014への大量件数インサート

Question

###前提・実現したいこと
VB.NET2013でAccess（mdb形式）のテーブルからSQLServer2014のテーブルに10万件のデータを
Insert処理しようと考えています。
ところが、コードを実行すると、以下のエラーメッセージが発生します。
「-2147467259 メモリー不足です。」
Providerを「Microsoft.ACE.OLEDB.12.0」にすると、
1万件は問題なくInsertされますが、それ以上になると「メモリ不足」が発生します。
※エラーが発生する場所は「mCon.Execute(strSQL)」です。

ADOでも他の方法でもよいですので、メモリ不足にならず一括でSQLServerに
Insertできる方法を教えてください。
一括では無理である場合は他の方法を教えていただければ助かります。
※設定でもかまいません。

よろしくお願いいたします。

###発生している問題・エラーメッセージ

「-2147467259 メモリー不足です。」

###該当のソースコード

'ADODBオブジェクト作成
mCon = New ADODB.Connection

'Accessオープン
mCon.Open("Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=c:\test\xxx.mdb;" & _
          "Persist Security Info=false")

'SQL文初期化
strSQL = ""

'SQLServerへのINSERT文発行
strSQL = "INSERT INTO [odbc;Driver={SQL Server};" & _
         "Server=xx.xx.xx.xx;Database=DB;" & _
         "UID=TEST;PWD=TEST1].Meisai " & _
         "SELECT * FROM 明細"

'処理実行
mCon.Execute(strSQL)

###試したこと
以下、OLEでも試しましたが、やはり同じ「メモリ不足」が発生します。
※エラーが発生する場所は「SQL.ExecuteNonQuery()」です。

Dim Cn As System.Data.OleDb.OleDbConnection
Dim SQL As System.Data.OleDb.OleDbCommand

Cn = New System.Data.OleDb.OleDbConnection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\test\xxx.mdb;" & _
"Persist Security Info=false"
Cn.Open()

SQL = cn.CreateCommand
SQL.Connection = cn

SQL.CommandText = "INSERT INTO [odbc;Driver={SQL Server};" & _
"Server=xx.xx.xx.xx;Database=DB;" & _
"UID=TEST;PWD=TEST1].Meisai " & _
"SELECT * FROM 明細"

SQL.ExecuteNonQuery() '実行

###補足情報(言語/FW/ツール等のバージョンなど)
言語：VisualStudio 2013 VB.net
DB :SQLServer2014 Access2013（mdb形式）

Answer 1

大量なレコードはBULK INSERTを使っておくのがベターだとは思いますが、アプリ側でロジックを噛ませたい等できない場合は、一気に処理しようとせずある程度細切れに処理してあげるようにした方がよいです

細切れに処理する方法もいろいろあろうかと思いますが、例えば

Using sql_c As New SqlClient.SqlConnection  'SqlServerへのConnection

            With sql_c
                .ConnectionString = "接続文字列"
            End With

            Dim t As SqlClient.SqlTransaction   'トランザクション処理をする
            t = sql_c.BeginTransaction

            Try

                Using mdb_c As New OleDb.OleDbConnection  'mdbへのConnection

                    With mdb_c
                        .ConnectionString = "接続文字列"
                    End With

                    'mdb側でSelectしてSqlServer側にInsertする処理を適宜書く

                End Using

                t.Commit()  '成功したらCommit
            Catch ex As Exception
                t.Rollback()  '問題が起きたらRollbackして無かったことにする
            End Try

End Using

みたいな感じでうまくいけばいいのですが

Comments

[kei5]

ご連絡有難うございます。
試してみたいと考えていますが、「mdb側でSelectしてSqlServer側にInsertする処理」の部分がどのように記載してよいか分からないのです。前回のようにADOを利用する方式だとABENDするような気もしますし、ここの部分をご教授いただければ大変助かります。

[kei5]

一応、SQL文は記載しましたがSQLServerにはLimit文が無いので、細切れにはできない様な気がします。Usingを利用して細切れにInsert処理を行うことが可能なのでしょうか？勉強不足もあり、ナレッジが足りない部分もあるので大変申し訳ありませんが、その部分もご教授いただければ大変助かります。

Answer 2

こちらの記事などを参考にすると、ロールバックのための領域が不足しているのではと推測されます。
http://qa.atmarkit.co.jp/q/6069

数千件ごとくらいずつでコミットしつつ処理するのが妥当かと思います。
もしくは、使用が可能ならSSISを使った方が楽かもしれません。
https://msdn.microsoft.com/ja-jp/library/ms141134(v=sql.120).aspx

Comments

[kei5]

ご連絡有難うございます。ロールバック領域はどのように確保するのでしょうか？そもそもその部分が分からないのです。また、SSISで行うと一つの業務システムの中では完結しないと考えています。あくまでもSQLServer側から実行させるイメージだと思いますので。
また、AccessからのBULKINSERTの方法や他の方法で回避できるのであればが詳しく教えていただければ非常に助かります。
勉強不足で申し訳ありませんがよろしくお願いいたします。

[ttyp03]

私も調べながら答えているので明確な答えは出せないのですが、ロールバック領域についてはMangement Studioからデータベースの設定で変更できないでしょうか。
ファイルの種類の「ログ」がそうかと。

SSISを使う場合でも、パッケージ自体はSQLServerの中に保持されるので、システムとして分離されるということはないと思います。
ただSQLServer側からMDBを参照できないといけないので、環境的には無理かもしれませんね。

BULK INSERT を使う方法も可能かと思います。
Accessからテーブルを一旦CSV等でエクスポートしてから、それをBULK INSERTするなど。
結構手間がかかりそうですが。

[kei5]

ご連絡有難うございます。恐らく、ACCESS側のメモリが不足していると思われます。ですから、Providerを変更することによってInsert可能件数が違ってきているのかと思います。先ほどからACCESS側のメモリ確保方法を探しているのですが、なかなか見つからない状態です。
SSISは指摘されたとおり、少し難しいような気がしています。全てはVBの中のWindowsアプリケーションで処理をかけているので。
BULKINSERTも恐らくEXCELに出力した状態からなので、一番有力だと考えていますが、勉強不足もありBULKINSERTの方法も明確には分かっておらず、教えていただければ助かります。

[ttyp03]

ACCESS側はSELECTしているだけなので、メモリ不足というのは想像し難いのですが、この記事は参考になりますでしょうか。
http://www.ilovex.co.jp/blog/system/cat818/access-3.html
この記事は更新処理なのでちょっと違うかもしれませんが。

BULK INSERT の件。少し考えたのですが、こちらもSSIS同様、少々難しいかもしれません。
ファイルからINSERTすることになるので、SQLServerからそのファイルが見えないといけないので、SSISと同じ問題に直面することになります。

思うに、こういった巨大なテーブルの更新処理は、クライアントの画面でやるよりは、サーバー側でバッチ処理した方がよいと思いますね。
ただ事情もあるでしょうから、画面からやるとなると、やはり分割してコミットしていくのが確実かと思います。
その代り処理時間はものすごくかかりそうですが…。