DOBON.NET DOBON.NETプログラミング掲示板過去ログ

MDBからSQLserverへの移行

環境/言語:[VB.NET2005]
分類:[.NET]

VB.NETを使ってMDBからSQLserverへテーブルデータの移行をやりたいんですが、どうすればできるのかいいかわかりません。

アクセスやSQLserver側から移行できる機能があるのは調べているうちにわかったのですが、それらを使わず。プログラミングで行いたいので、どうかご教授願います。

ちなみに、両方のDBには接続に関しては大丈夫です。
■No26019に返信(勉強中さんの記事)
> ちなみに、両方のDBには接続に関しては大丈夫です。

手順がわからないってこと?

手順としてであれば、

事前準備: あらかじめ 移行先のSQLServer に 移行元のAccess と同じ構成のテーブルを作成しておく

プログラムの手順
1. SQLServer 及び Access にコネクト
2. Accessの移行に該当するテーブルをオープンする
3. レコードのデータを取得してSQLServerのテーブルへインサートするSQLを作成する
4. SQLServerへ作ったSQLを発行する
5. Accessのレコードがなくなるまで 3 → 4 を繰り返す

でいいんじゃない?
■No26021に返信(Algolさんの記事)

ありがとうございます。
手順1,2まではできてるのですが3以降がピンとこず…というのが現在の状況です。具体的なプログラムを探しているのですが、調べても出てこないので。
■No26022に返信(勉強中さんの記事)
> ■No26021に返信(Algolさんの記事)
>
> ありがとうございます。
> 手順1,2まではできてるのですが3以降がピンとこず…というのが現在の状況です。具体的なプログラムを探しているのですが、調べても出てこないので。

んーどのあたりを調べたのかわかりませんが、
少なくとも MSDN は見ておきましょ。
その程度であればサンプルが載っていますので。

関連すると思われるリンクを載せておきます。

・System.Data.Odbc名前空間
http://msdn.microsoft.com/ja-jp/library/system.data.odbc(VS.80).aspx
・System.Data.Odbc.OdbcCommand クラス
http://msdn.microsoft.com/ja-jp/library/system.data.odbc.odbccommand(VS.80).aspx
・System.Data.Odbc.OdbcDataReader クラス
http://msdn.microsoft.com/ja-jp/library/system.data.odbc.odbcdatareader(VS.80).aspx
■No26022に返信(勉強中さんの記事)

あと、SQLServer の SQL構文は Transact-SQL ってので出来ています。
この辺のサンプルでインサート文作ってみては?

Transact-SQL リファレンス
http://technet.microsoft.com/ja-jp/library/ms189826(SQL.90).aspx
■No26023に返信(Algolさんの記事)
>具体的なプログラム
テーブルのデータを取得までは調べれれるのですがSQLserverのテーブルへインサートするSQLの作成、SQLserverにSQLを発行するという流れですね。
中々、ここが載っているところがなくて。

Accseceのレコードがなくなるまでの部分はfor文とかループで回せばいいというのは気づいたのですが。
■No26027に返信(勉強中さんの記事)
> テーブルのデータを取得までは調べれれるのですがSQLserverのテーブルへインサートするSQLの作成、SQLserverにSQLを発行するという流れですね。
> 中々、ここが載っているところがなくて。

まぁ、アプリケーションごとに違う部分ですから普通はサンプルって少ないでしょうねぇ。
あと、勉強中さんの問題点の切り分けが必要かなと思います。
いっしょくたにして考えると訳わからなくなりますよ。(^^;

ちなみに実験用の自作サンプルって作って動かしてみましたか??
作ってあるなら、それを示してください。(重要なところは隠してね
作ってないなら、今から作ってから示してください。

勉強中さんが解らない部分はコメントにして示すと回答しやすくなります。
T-SQL文一発なら・・・

insert into
[SQL_TableName]
select
*
FROM
OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'hogehoge.mdb';'admin';'',
[MDB_TableName])

上記を、Executeしたらhogehoge.mdbのMDB_TableNameのところから
全レコードを、SQL Server側DBのSQL_TableNameにInsertできます。

where句を書けば、条件設定でのSelectができますし・・・

※ あくまでDBのテーブル構成は同じと言う前提です。
  違う場合は、Select文側でフィールドをチョイスするか
  as にてフィールド名をSQL側に合わせればOK〜

以上。参考まで
■No26028に返信(Algolさんの記事)
ソースを載せます

Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class Form1
Dim cn As System.Data.SqlClient.SqlConnection

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim MDB_DB As New DataTable
Dim SQL_DB As New DataTable
Dim A As String
Try
'SQL_SERVER接続
SQL_DB = GetSQLDataSet("SELECT * FROM SQLテーブル", 1)

If SQL_DB.Rows.Count <> 0 Then
MsgBox("データあり")

'** データ1件目表示
MsgBox(SQL_DB.Rows(0).Item("データ1"))
MsgBox("データ書き込み")

'** データ書き込み部分

Else
MsgBox("データなし")
End If
'*****************************************************************

'MDB接続
MDB_DB = GetSQLDataSet("SELECT * FROM MDBテーブル", 2)

If MDB_DB.Rows.Count <> 0 Then
MsgBox("データあり")


'** データ1件目表示
'MsgBox(MDB_DB.Rows(0).Item("フィールド1"))
Else
MsgBox("データなし")
End If

Catch ex As Exception
MsgBox(ex.Message.ToString)
Finally
SQL_DB.Dispose()
MDB_DB.Dispose()
End Try


End Sub

Public Sub ActExcute(ByVal strcOMMAND As String)

Dim retDt As New DataTable
Using con As New SqlConnection
Dim cmd As New SqlCommand

'接続関連プロパティ設定
Me.SetConnection(con, cmd)

'SQL文設定
cmd.CommandText = strcOMMAND

'テーブルからレコード取得
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
da.Fill(retDt)

End Using
End Sub
Public Function GetSQLDataSet(ByVal strCommand As String, ByVal DBType As Integer) As DataTable

Dim retDt As New DataTable 'リターン値

Try
'** SQL SERVER接続
If DBType = 1 Then
Using con As New SqlConnection
Dim cmd As New SqlCommand

'接続関連プロパティ設定
Me.SetConnection(con, cmd)

'SQL文設定
cmd.CommandText = strCommand

'テーブルからレコード取得
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
da.Fill(retDt)

End Using

'** MDB 接続
Else
Using con As New OleDbConnection
Dim cmd As New OleDbCommand

'接続関連プロパティ設定
Me.mdbConnection(con, cmd)

'SQL文設定
cmd.CommandText = strCommand

'テーブルからレコード取得
Dim da As New OleDbDataAdapter
da.SelectCommand = cmd
da.Fill(retDt)

End Using
End If

Catch ex As Exception
Throw New Exception _
("clsSQLDBIO.GetSQLDatSetで例外発生:" & ex.ToString)

End Try

'----------< ◆戻値を設定してリターン >----------
Return retDt

End Function
Public Sub SetConnection(ByRef con As SqlConnection, _
ByRef cmd As SqlCommand)

Try
'----------< 接続関連プロパティ設定 >----------
'変数の宣言
Dim settings As String
'接続する端末名
Dim ServerName As String = "サーバー名"
'接続するデータベース名
Dim DBName As String = "SQLDB"
'ユーザ名
Dim UserID As String = "ユーザー"
'パスワード
Dim Password As String = "パス"

'接続文字列をapp.configファイルから取得
'** データベース接続情報
settings = "Server =" & ServerName & ";" & _
"User ID =" & UserID & ";" & _
"Password =" & Password & ";" & _
"Initial Catalog =" & DBName

If settings Is Nothing Then
'接続文字列取得エラー
Throw New Exception _
("接続文字列がapp.configに未登録.")
Else
'接続文字列の設定
con.ConnectionString = settings

'SqlCommand.Connectionプロパティの設定
cmd.Connection = con

End If

Catch ex As Exception
Throw New Exception _
("SetConnectionで例外発生." & ex.ToString)
End Try

End Sub

Public Sub mdbConnection(ByRef con As OleDbConnection, _
ByRef cmd As OleDbCommand)

Try
'----------< 接続関連プロパティ設定 >----------
'接続するMDB名
Dim MDBName As String = "MDBファイル"

' DB接続文字列の設定
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & MDBName

' コネクションの設定
cmd.Connection = con

' DB接続を開く
con.Open()


Catch ex As Exception
Throw New Exception _
("mdbConnectionで例外発生." & ex.ToString)
End Try

End Sub

End Class

以上です
■No26036に返信(勉強中さんの記事)

ソース見てみました。

先ほど手順を示しましたが…
> 1. SQLServer 及び Access にコネクト
> 2. Accessの移行に該当するテーブルをオープンする
> 3. レコードのデータを取得してSQLServerのテーブルへインサートするSQLを作成する
> 4. SQLServerへ作ったSQLを発行する
> 5. Accessのレコードがなくなるまで 3 → 4 を繰り返す

勉強中さんは

> '** データ書き込み部分

の部分で処理しようと思っているって事でいいんですね?
その場所は、上記手順で言う処の 1. と 2. の間になります。
そこで取得したデータテーブルに値を書き込んでもSQLServerには反映される訳ではないので、
書き込む為にデータテーブルを取得をする必要はありません。

書き込み処理をするのであれば

> ** データ1件目表示
> 'MsgBox(MDB_DB.Rows(0).Item("フィールド1"))

の辺りになると思います。

まずは、そこで Rowsのカウントでループしつつ insert文 を作成するコードを書きましょう。
次に、ExecuteNonQueryメソッドを使い、作成した insert文 を発行するコードを書けば完成ですね。

SQLを発行するサンプルはMSDNにありますので、そちらを参照のこと。

・SqlCommand クラス
http://msdn.microsoft.com/ja-jp/library/system.data.sqlclient.sqlcommand(VS.80).aspx

・SqlCommand.ExecuteNonQuery メソッド
http://msdn.microsoft.com/ja-jp/library/system.data.sqlclient.sqlcommand.executenonquery(VS.80).aspx

あと注意点。

> Dim MDB_DB As New DataTable
> Dim SQL_DB As New DataTable

ここで使っている New は意味がないので不要です。

普通は、この様にすべきです。

Dim MDB_DB As DataTable = Nothing
Dim SQL_DB As DataTable = Nothing

なぜそうすべきなのは、今回の件とは別なので自分で調べてください。

>オショウさん

OPENROWSET ってサーバのローカル上に mdbファイル を置いておかないと出来ないんじゃないでしたっけ??
あと、勉強中さんは、文字通り「勉強中」の様ですので、楽な方法で教えない方がいいかとなと私は思います。
そうでなければ勉強中さんの勉強にはならないかと(笑
■No26040に返信(Algolさんの記事)
> OPENROWSET ってサーバのローカル上に mdbファイル を置いておかないと出来ないんじゃないでしたっけ??

  をを〜失礼しました。
  その通り・・・

以上。
■No26041に返信(オショウさんの記事)
ありがとうございます。

ご指摘もあり、書き込みスペースに関しては間違っているということに気づき。
SQLコマンドの作成に移ったのですが、どのように記述してよいのかがわからず
お手上げ状態になっています。
リンクを貼ってくれたMSDNの奴を使うのは理解してはいるのですが、肝心のSQLがピンとこず…。

>>まずは、そこで Rowsのカウントでループしつつ insert文 を作成するコードを書きましょう

ここのあたりの『ループさせてまわす』という事は考えておりinsert文も使うというのも調べているうちにわかったのですが、では実際にどう組み立てるのかとなるとまったく手が出ません。自分で考える分にはもう限界を感じており、よろしければヒントではなく正解を教えていただけると幸いです。
■No26044に返信(勉強中さんの記事)
> ここのあたりの『ループさせてまわす』という事は考えておりinsert文も使うというのも調べているうちにわかったのですが、では実際にどう組み立てるのかとなるとまったく手が出ません。自分で考える分にはもう限界を感じており、よろしければヒントではなく正解を教えていただけると幸いです。

  SQL Server側はテーブル構造は存在しデータが無い状態と言う前提なら・・・

  例えばこんな風・・・

    Imports System.Data
    Imports System.Text

    Private Function GetMdbConnection(ByVal MdbPath As String) As String

        Dim OleSQL As OleDb.OleDbConnectionStringBuilder

        OleSQL = New OleDb.OleDbConnectionStringBuilder

        With OleSQL
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .DataSource = MdbPath
        End With

        Return OleSQL.ToString

    End Function

    Private Function GetDBConnectString() As String

        Dim AdoSQL As SqlClient.SqlConnectionStringBuilder

        AdoSQL = New SqlClient.SqlConnectionStringBuilder

        With AdoSQL
            .DataSource = "LocalHost"
            .InitialCatalog = "TEST"
            .IntegratedSecurity = False
            .UserID = "sa"
            .Password = "hogehoge"
            .ConnectTimeout = 2
        End With

        Return AdoSQL.ToString

    End Function

    Private Sub MdbToSql(ByVal MdbPath As String)

        Dim bRet As Boolean

        Using dsMdb As DataSet = New DataSet
            Using OleMdb As OleDb.OleDbConnection = New OleDb.OleDbConnection(GetMdbConnection(MdbPath))
                Try
                    OleMdb.Open()
                    bRet = True
                Catch ex As Exception
                    bRet = False
                End Try

                If bRet Then
                    Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
                        Dim OleSQL As StringBuilder

                        OleSQL = New StringBuilder

                        With OleSQL
                            .Length = 0
                            .Append("select ")
                            .Append("* ")
                            .Append("from ")
                            .Append("ItemMaster")
                        End With

                        Using OleCMD As OleDb.OleDbCommand = OleMdb.CreateCommand
                            With OleCMD
                                .CommandType = CommandType.Text
                                .CommandTimeout = OleMdb.ConnectionTimeout
                                .CommandText = OleSQL.ToString
                            End With

                            da.SelectCommand = OleCMD

                            Try
                                da.Fill(dsMdb, "ItemMaster")
                            Catch ex As Exception
                                bRet = False
                            End Try
                        End Using
                    End Using

                    OleMdb.Close()
                End If
            End Using

            If bRet And dsMdb.Tables("ItemMaster").Rows.Count > 0 Then

                Using SqlDB As SqlClient.SqlConnection = New SqlClient.SqlConnection(GetDBConnectString())
                    Try
                        SqlDB.Open()
                        bRet = True
                    Catch ex As Exception
                        bRet = False
                    End Try

                    If bRet Then
                        Dim iRet As Integer

                        Using dsSql As DataSet = New DataSet
                            Using da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
                                Dim AdoSQL As StringBuilder

                                AdoSQL = New StringBuilder

                                With AdoSQL
                                    .Length = 0
                                    .Append("select ")
                                    .Append("ItemName,")
                                    .Append("Input,")
                                    .Append("Output ")
                                    .Append("from ")
                                    .Append("ItemMaster ")
                                End With

                                Using AdoCMD As SqlClient.SqlCommand = SqlDB.CreateCommand
                                    With AdoCMD
                                        .CommandType = CommandType.Text
                                        .CommandTimeout = SqlDB.ConnectionTimeout
                                        .CommandText = AdoSQL.ToString
                                    End With

                                    da.SelectCommand = AdoCMD
                                End Using

                                With AdoSQL
                                    .Length = 0
                                    .Append("insert into ")
                                    .Append("ItemMaster ")
                                    .Append("(")
                                    .Append("ItemName,")
                                    .Append("Input,")
                                    .Append("Output) ")
                                    .Append("values(")
                                    .Append("@ItemName,")
                                    .Append("@Input,")
                                    .Append("@Output)")
                                End With

                                Using AdoCMD As SqlClient.SqlCommand = SqlDB.CreateCommand
                                    With AdoCMD
                                        .CommandType = CommandType.Text
                                        .CommandTimeout = SqlDB.ConnectionTimeout
                                        .CommandText = AdoSQL.ToString
                                        .Parameters.Add("@ItemName", SqlDbType.NVarChar, 50, "ItemName")
                                        .Parameters.Add("@Input", SqlDbType.Int, 4, "Input")
                                        .Parameters.Add("@Output", SqlDbType.Int, 4, "Output")
                                    End With

                                    da.InsertCommand = AdoCMD
                                End Using

                                Try
                                    iRet = da.Fill(dsSql, "ItemMaster")
                                Catch ex As Exception
                                End Try

                                For Each row As DataRow In dsMdb.Tables("ItemMaster").Rows
                                    Dim newrow As DataRow = dsSql.Tables("ItemMaster").NewRow
                                    For i As Integer = 0 To row.ItemArray.Length - 1
                                        newrow(i) = row.Item(i)
                                    Next
                                    dsSql.Tables("ItemMaster").Rows.Add(newrow)
                                Next

                                Try
                                    iRet = da.Update(dsSql, "ItemMaster")
                                    bRet = True
                                Catch ex As Exception
                                    iRet = 0
                                    bRet = False
                                End Try

                            End Using
                        End Using

                        If iRet > 0 Then
                            MsgBox("Insert!!", MsgBoxStyle.OkOnly, "Mdb To Sql")
                        End If

                        SqlDB.Close()
                    End If
                End Using
            End If
        End Using

    End Sub

  テーブル名やフィールドに関しては、修正追加して下さい。

※ Insert文で1件づつ挿しこむと言う方法もありますが、
  MDBからSQL Serverへデータ移行すると言う前提で何度
  も使わないのであれば、こんなんでよいかと。

  ただMDBに膨大にデータがあるならメモリを食いますの
  で、1レコードつづ取ってきて挿しこむ方法がよいと
  思います。

  あくまで参考

以上。
■No26044に返信(勉強中さんの記事)
■No26045に返信(オショウさんの記事)

> ここのあたりの『ループさせてまわす』という事は考えておりinsert文も使うというのも調べているうちにわかったのですが、では実際にどう組み立てるのかとなるとまったく手が出ません。自分で考える分にはもう限界を感じており、よろしければヒントではなく正解を教えていただけると幸いです。

オショウさんはやさしいですね。
私は、相手が育たないようなサンプルもしくはスバリそのまま正解を書く気はないので辞退させていただきます。

勉強中さん頑張ってください。
■No26045に返信(オショウさんの記事)

ありがとうございます、使用しているSQLserverには一応テーブルもあり、データもありです。
オショウさんのプログラムを参考に、プログラムを書いていますが。
Fillの前にSELECTをやれ見たいなメッセージが出てそこで中断してしまいます。
もしかして、またSELECT構文を入れなくてはいけないのでしょうか。

また、私のプログラムではDataSetはメインの方に入れてませんがそれでも動かせる事は可能でしょうか?
■No26048に返信(勉強中さんの記事)
> Fillの前にSELECTをやれ見たいなメッセージが出てそこで中断してしまいます。
> もしかして、またSELECT構文を入れなくてはいけないのでしょうか。

  SQL Server側からデータが無くてもSELECTしないと
  DataSetにインサートすべきデータを追加しても、
  インサートできません。

> また、私のプログラムではDataSetはメインの方に入れてませんがそれでも動かせる事は可能でしょうか?

  メインとは、SQL Server側のDataSetと言うことなら、コードの
  通りしないと、インサートされません。

※ 非接続型を使ってますので、解りにくいかと思いますが
  DataSet使わない接続型なら、1レコードづつMDB側から
  取得してSQL側にインサートするSQL文を実行しないと、
  できませんネ・・・

  どっちがいいのでしょうか?

※ 動くコードを掲載しましたが、DBの構成が解らないので
  最低限の修正は必要です。SQL文を修正・・・
  なかなか一筋縄ではいきませんネ!

  頑張って下さい!

以上。
■No26049に返信(オショウさんの記事)
ありがとうございます!かなり勉強になりました。

>>非接続型を使ってますので、解りにくいかと思いますが
  DataSet使わない接続型なら、1レコードづつMDB側から
  取得してSQL側にインサートするSQL文を実行しないと、
  できませんネ・・・

  どっちがいいのでしょうか?

何か面倒くさそうなのでDataSetでやろうと思い。
Public Function GetSQLDataSet(ByVal strCommand As String, ByVal DBType As Integer) As DataTable
の部分を

Public Function GetSQLDataSet() As DataSet

と変えて、それにあわせて諸々の中身を変更する事で可能になりました。

一応、まだデータの入れ替えの段階の不具合は起きているのですが。これ以上やると冗長的になりますし、とりあえずひと段落ついたのではと思いますので、これで打ち切らせてもらいます。今ある問題は他のところで調べてわからなければ段階を踏まえて一つ一つ質問していこうと考えています。
データーベースについて色々と勉強になりました、アドバイスをくれた方々ありがとうございます。
解決済み!

DOBON.NET | プログラミング道 | プログラミング掲示板