↑の方ではテーブルの構造をコピーしたので今度はデータをコピーするプログラム。
'データベースA→データリーダーに読み込み→データベースBに書き込み
ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=DATABASE.MDF;Integrated Security=SSPI;"
ConnectionString2 = "Data Source=(local);Initial Catalog=AoM;Integrated Security=SSPI;"
SQL = "Select * From " & TableName
conn = New SqlConnection(ConnectionString)
conn2 = New SqlConnection(ConnectionString2)
cmd = New SqlCommand(SQL, conn)
conn.Open()
DR = cmd.ExecuteReader
DR.Read()
schemaTable = DR.GetSchemaTable()
SS = ""
Dim i As Integer
'For Each myRow In schemaTable.Rows
' SQL2 = "Insert into " & TableName & "("
' For Each myCol In schemaTable.Columns
' 'SS = SS & myCol.ColumnName & " = " & myRow(myCol).ToString() & "<br>"
' Next
' 'SS += "----------------------------------------------" & "<br>"
'Next
Dim SQL2b As String
'While DR.Read 'WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW'いったんリードしてるのでここではリードしない
Do
SQL2 = "Insert into " & TableName & "("
SQL2b = ") Values ("
For i = 0 To DR.FieldCount - 1
If Not i = 0 Then
SQL2 += ","
SQL2b += ","
End If
SQL2 += "[" & DR.GetName(i) & "]"
'文字列は' 'で囲むので列のタイプがわからないといけない。
schemaTable = DR.GetSchemaTable()
For Each myRow In schemaTable.Rows
For Each myCol In schemaTable.Columns
If myCol.ColumnName = "DataType" Then
If myRow(myCol.ColumnName).ToString = "System.DateTime" Or myRow(myCol.ColumnName).ToString = "System.String" Then
If Not IsDBNull(DR(i)) Then
SQL2b += "'" & DR(i) & "'"
Else
SQL2b += "Null"
End If
End If
GoTo LandB
End If
Next
Next
LandB:
' If schemaTable.Rows(DR.GetName(i))("DataType") Then
'SQL2b += schemaTable.Rows(0)("DataType").ToString
Next
'GoTo LandA
SQL2 += SQL2b & ")"
cmd2 = New SqlCommand(SQL2, conn2)
conn2.Open()
num2 = cmd2.ExecuteNonQuery
conn2.Close()
'End While 'WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW
Loop While DR.Read
' GridView5.DataSource = DR
' GridView5.DataBind()
LandA:
DR.Close()
conn.Close()
Label3.Text = SQL2
