vb 用BULK批量上传TXT数据到SQL服务器

Sub updata_Close_Inquiry()
        'stable 要导入的表，Inquiry为临时表，存放即将导入的数据，并在Inquiry表中进行格式转换
        Dim ds As String
        Dim i, j
        Dim ss = 0
        ds = ""
        For i = LBound(arr) + 1 To UBound(arr)  'arr为需要更新的数据，如果SQL中存在，删除，再重新导入arr的数据
            ds = "'" & arr(i, 53) & arr(i, 54) & "'," & ds 'ds为主键的内容，即需要在SQL中删除的数据
        Next
        ds = Left(ds, ds.Length - 1) '对ds内容进行修改
        ds = Replace(ds, "' ", "")

        Dim rs As SqlCommand
        Dim cnn As SqlConnection
        Dim sqlstr1 As String
        cnn = New SqlConnection("server=" & ssever & ";database=" & sdatabase & ";uid=sa;pwd=sa")
        sqlstr1 = "USE Sales_Order IF COL_LENGTH('" & stable & "', 'Order_No') IS  NULL alter table " & stable & "  add Order_No nvarchar(255) "  'SQL主键是否存在，如果存在，删除，在之前的操作中可能有增加SQL的主键
        rs = New SqlCommand(sqlstr1, cnn)
        Try
            cnn.Open()
            rs.ExecuteNonQuery()
        Catch
            MsgBox("Error:0", 16, "Attention")
            Exit Sub
        Finally
        End Try
        cnn.Close()
        sqlstr1 = "USE Sales_Order UPDATE " & stable & " SET [Order_No] = convert(varchar(255),convert(decimal(20,0),[Order No#]))+[Line No#]" '重新增加SQL主键，并填充主键内容
        rs = New SqlCommand(sqlstr1, cnn)
        Try
            cnn.Open()
            rs.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error:0" & Chr(10) & ex.Message, 16, "Attention")
            Exit Sub
        Finally
        End Try
        cnn.Close()
        sqlstr1 = "USE Sales_Order DELETE FROM " & stable & " Where [Order_No] in (" & ds & ")" '删除在ds中的数据
        rs = New SqlCommand(sqlstr1, cnn)
        Try
            cnn.Open()
            ss = rs.ExecuteNonQuery() 'ss为返回删除的行数
        Catch ex As Exception
            MsgBox("Error:0" & Chr(10) & ex.Message, 16, "Attention")
            Exit Sub
        Finally
        End Try
        cnn.Close()
        EditRecord = EditRecord + ss '记录删除的行数
        'sqlstr1 是用BULK上传TXT文件的语句
        sqlstr1 = "USE Sales_Order IF COL_LENGTH('" & stable & "', 'Order_No') IS NOT NULL  ALTER TABLE " & stable & " DROP COLUMN Order_No " '删除Order_No主键，在TXT文档中没有该列
        sqlstr1 = sqlstr1 & "TRUNCATE TABLE Inquiry; " '清除Inquiry表的数据
        sqlstr1 = sqlstr1 & "alter table Inquiry alter column [Order Date] nvarchar(255) alter table Inquiry alter column [Shipment Date] nvarchar(255) alter table Inquiry alter column [Closed Date] nvarchar(255) alter table Inquiry alter column [Fapiao Date] nvarchar(255) alter table Inquiry alter column [Nota Date] nvarchar(255) " '按需求将Inquiry表的格式全部修改为nvarchar(255)，防止TXT文档导入时出错
        sqlstr1 = sqlstr1 & "BULK INSERT Inquiry FROM '" & stxt & "' WITH (FIELDTERMINATOR ='\t', ROWTERMINATOR= '\n'); " '将TXT文档导入Inquiry表中 FIELDTERMINATOR：分列符  ROWTERMINATOR：分行符
        sqlstr1 = sqlstr1 & "alter table Inquiry alter column [Order Date] date alter table Inquiry alter column [Shipment Date] date alter table Inquiry alter column [Closed Date] date alter table Inquiry alter column [Fapiao Date] date alter table Inquiry alter column [Nota Date] date " '按照需求将Inquiry表的格式修改成stable格式
        rs = New SqlCommand(sqlstr1, cnn) '执行
        Try
            cnn.Open()
            rs.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error:0" & Chr(10) & ex.Message, 16, "Attention")
            Exit Sub
        End Try
        cnn.Close()
        sqlstr1 = "USE Sales_Order insert into " & stable & " select * from Inquiry "  '将Inquiry表中的数据导入到stable表中，并记录导入的行数
        rs = New SqlCommand(sqlstr1, cnn)
        Try
            cnn.Open()
            ss = rs.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error:0" & Chr(10) & ex.Message, 16, "Attention")
            Exit Sub
        End Try
        cnn.Close()
        UpdataRecord = UpdataRecord + ss
        sqlstr1 = "USE Sales_Order TRUNCATE table Inquiry; update  " & stable & " set [Line No#]  = replace([Line No#],'''','');update " & stable & " set [Line No#]  = replace([Line No#],' ','');"  '收尾，Inquiry表中的数据，对stable的文本进行处理
        rs = New SqlCommand(sqlstr1, cnn)
        Try
            cnn.Open()
            rs.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error:0" & Chr(10) & ex.Message, 16, "Attention")
            Exit Sub
        End Try
        cnn.Close()

        cnn = Nothing
        rs = Nothing
        If ss <> UBound(arr) - 1 Then
            MsgBox(stxt & "导入失败", 16)
        End If
    End Sub