質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.99%

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

受付中

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 4,407

kei5

score 10

前提・実現したいこと

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形式)

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

0

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/30 14:21

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

    キャンセル

  • 2016/06/30 14:35

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

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

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

    キャンセル

  • 2016/06/30 17:02

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

    キャンセル

  • 2016/07/01 10:55

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

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

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

    キャンセル

0

大量なレコードは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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/07/01 09:35

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

    キャンセル

  • 2016/07/01 10:22

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.99%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる