主页 / coding / 问题 / 79421420
Accepted
gchq
Asked: 2025-02-07 23:35:05 +0800 CST

sqlBulkCopy WriteToServer 在计算字段上抛出错误（即使它们未包含在 INSERT 语句中）

  • 772

将数据从 SQL Anywhere 移动到 SQL Server，经过几次故障后，一切进展顺利，直到我收到错误

无法修改列“A_Future”，因为它是计算列或 UNION 的结果’

公平地说，我正在从 SELECT * 插入数据——因此使用它仅返回未计算的列。

 'Remove computed columns from the SELECT query
 Dim DR() As DataRow = MSSQLDT.Select("Computed = 'N'")
 Dim vSelectedRows As Integer = DR.Count
 Dim vCurrentRow As Integer = 0
 strSQL = "SELECT "
 For Each Row In DR
 strSQL += Row("Name")
 vCurrentRow += 1
 If vCurrentRow = vSelectedRows Then
     strSQL += " "
 Else
     strSQL += ", "
 End If
Next
strSQL += "FROM " & vTable

这将返回以下查询字符串...

SELECT Transaction_ID, Debit, Credit, Paid, P_Description, Document_Date, Supplier_ID, Nominal_Transaction, HOA_Code, Document_Saved, Document_ID, Document_No, Supplier_Inv_No, Type, Paid_Date, Part_Paid, Open_Editing, Editing_Name, Updated_Name, Updated, Reserve_Item, Hold, eCheck_Pending, eSig_Required, eSigOne_ID, eSigTwo_ID FROM A_Purchase_Ledger

.. 并且它不包含任何计算列。再次运行，但它仍然抛出相同的错误（对于所有计算列）

Microsoft.Data.SqlClient.SqlException (0x80131904): The column "A_Future" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_Current" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_30" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_60" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_90" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_Older" cannot be modified because it is either a computed column or is the result of a UNION operator.

现在这没有任何意义，因为我没有尝试向其中插入数据，除非存在SqlBulkCopy我不知道的怪癖？

选择数据被添加到DataTable然后它进入这个函数

  Public Function BulkUpdate_DataMS(DT As DataTable, HOAID As Integer, IsHASoftware As Boolean, TableName As String) As Boolean
  Try
      Using Conn As New Microsoft.Data.SqlClient.SqlConnection
          If IsHASoftware = True Then
              Conn.ConnectionString = HASConString
          Else
              Conn.ConnectionString = ReturnConnStringMS(HOAID)
          End If
          Conn.Open()
          Using vTrans = Conn.BeginTransaction
              Using vBulk As New Microsoft.Data.SqlClient.SqlBulkCopy(Conn, Microsoft.Data.SqlClient.SqlBulkCopyOptions.Default, vTrans)
                  vBulk.DestinationTableName = TableName
                  vBulk.WriteToServer(DT)
              End Using
              vTrans.Commit()

          End Using
          Conn.Close()

      End Using
      Return True
  Catch ex As Exception
      EmailError(ex, 222, PageName)
      Return False
  End Try
 End Function
sql-server

2 个回答

  1. Best Answer

    您需要设置，ColumnMappings否则它只会进行序数匹配。

    此外，无需事务，因为批量插入将使用其自己的事务。如果您有事务，则Using无需手动关闭连接。

    Public Function BulkUpdate_DataMS(DT As DataTable, HOAID As Integer, IsHASoftware As Boolean, TableName As String) As Boolean
    Try
        Using Conn As New SqlConnection(If(IsHASoftware, HASConString, ReturnConnStringMS(HOAID)))
            Using vBulk As New SqlBulkCopy(Conn, SqlBulkCopyOptions.Default)
                vBulk.DestinationTableName = TableName
                For Each col As DataColumn in DT.Columns
                    vBulk.ColumnMappings.Add(col.Name, col.Name)
                Next
                Conn.Open()
                vBulk.WriteToServer(DT)
            End Using
        End Using
        Return True
    Catch ex As Exception
        EmailError(ex, 222, PageName)
        Return False
    End Try
End Function

    您可能还应该考虑使用Async和Await来完成所有这些工作。

    执行异步的正确方法不是使用，而是直接在标记为的函数中批量插入时Task.Run使用AwaitAsync

    Public Async Function BulkUpdate_DataMS(DT As DataTable, HOAID As Integer, IsHASoftware As Boolean, TableName As String) As Task(Of Boolean)
    Try
        Using Conn As New SqlConnection(If(IsHASoftware, HASConString, ReturnConnStringMS(HOAID)))
            Using vBulk As New SqlBulkCopy(Conn, SqlBulkCopyOptions.Default)
                vBulk.DestinationTableName = TableName
                For Each col As DataColumn in DT.Columns
                    vBulk.ColumnMappings.Add(col.Name, col.Name)
                Next
                Await Conn.OpenAsync()    ' NOTE the async
                Await vBulk.WriteToServerAsync(DT)
            End Using
        End Using
        Return True
    Catch ex As Exception
        EmailError(ex, 222, PageName)
        Return False
    End Try
End Function

    DataTable此外，如果您正在从另一台服务器读取数据，则根本不需要将其保存在 a 中。您可以将从/DbDataReader获取的数据直接传递给/ 。ExecuteReaderExecuteReaderAsyncWriteToServerWriteToServerAsync

    • 2

  2. 这不是我想要的解决方案，需要遍历数千行，但批量更新似乎在涉及计算值的地方不起作用。这会检查是否有任何计算值，然后一次插入一个。如果有人有更好的解决方案，我很乐意听听。

    基本上，动态地拉动每个Table模式，将SQL Anywhere其更改为SQL Server，添加表，SQL Server然后插入数据

     Private Async Sub DataTransfer_Run(sender As Object, e As RoutedEventArgs)
 Try
     Dim DTTB As TBx = DataTransfer_Grid.FindName("DTTB")
     Dim vTable As String = DTTB.Text
     vImage = New LoadingImage
     LoadingStarted("Creating tables... Please wait...", vImage)
     Dim SB As New System.Text.StringBuilder
     SB.Append("IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" & vTable & "') ")
     SB.AppendLine()
     SB.Append("CREATE TABLE " & vTable & "(")
     SB.AppendLine()
     Dim vRowsTotal As Integer = MSSQLDT.Rows.Count
     Dim vRow As Integer = 0
     Await Task.Run(Sub()
                        For Each Row As DataRow In MSSQLDT.Rows
                            Dim vName As String = Row("Name")
                            Dim vType As String = Row("Type")
                            Dim vNulls As String = Row("Nulls")
                            Dim vDefault As String = Row("Default")
                            Dim vComputed As String = Row("Computed")
                            Dim vPrimary As String = Row("Primary")
                            If vComputed = "N" Then
                                SB.Append(vName & " " & vType)
                                If vPrimary = "Y" Then
                                    SB.Append(" NOT NULL IDENTITY(1,1) PRIMARY KEY")
                                ElseIf vComputed = "Y" Then
                                    SB.Append(vName & " AS " & vDefault)
                                ElseIf vNulls = "N" Then
                                    SB.Append(" NOT NULL")
                                End If

                                Select Case vDefault
                                    Case "autoincrement"
                                    'Do nothing
                                    Case ""
                                        'Do nothing
                                    Case Else
                                        SB.Append(" DEFAULT " & vDefault)
                                End Select

                            Else
                                SB.Append(vName & " AS " & vDefault)
                            End If

                            vRow += 1
                            If Not vRow = vRowsTotal Then
                                SB.Append(", ")
                                SB.AppendLine()
                            End If
                        Next

                    End Sub)
     SB.Append(");")
     DataTransfer_StatusRight.Content = "Creating tables for " & vTable
     Dim IsError As Boolean = False
     Await Task.Run(Sub()
                        strSQL = SB.ToString
                        If InsertData_DataMS(strSQL, DBID, 432, False) = False Then
                            IsError = True
                        End If
                    End Sub)

     If IsError = True Then
         LoadingCompleted("Error", "There was an error saving the table", vImage)
     Else
         'Add the data
         DataTransfer_StatusRight.Content = "Table was saved... Switching on IDENTITY_INSERT..."
         Dim vTotalRows As Integer = 0
         Await Task.Run(Sub()
                            strSQL = "SET IDENTITY_INSERT " & vTable & " ON"
                            If InsertData_DataMS(strSQL, DBID, 450, False) = False Then
                                IsError = True
                            End If
                            If IsError = False Then
                                strSQL = "SELECT COUNT(*) FROM " & vTable
                                vTotalRows = ReturnScalarInteger_Data(strSQL, DBID, False)
                            End If

                        End Sub)
         If IsError = False Then
             DataTransfer_StatusRight.Content = "IDENTITY_INSERT set to ON...Updating data of " & vTotalRows & " rows"

             Await Task.Run(Sub()
                                'Remove computed columns from the SELECT query
                                Dim DR() As DataRow = MSSQLDT.Select("Computed = 'N'")
                                Dim vSelectedRows As Integer = DR.Count
                                Dim vCurrentRow As Integer = 0
                                strSQL = "SELECT "
                                For Each Row In DR
                                    strSQL += Row("Name")
                                    vCurrentRow += 1
                                    If vCurrentRow = vSelectedRows Then
                                        strSQL += " "
                                    Else
                                        strSQL += ", "
                                    End If
                                Next
                                strSQL += "FROM " & vTable
                                Dim DT As DataTable = Nothing

                                If vCurrentRow > 0 Then
                                    'Update a row at a time
                                    Dim vInsert As String = "SET IDENTITY_INSERT " & vTable & " ON INSERT INTO " & vTable & "("
                                    Using DS As DataSet = ReturnDataSet_Data(strSQL, DBID, False)
                                        DT = DS.Tables(0).Copy
                                    End Using
                                    Dim vColumns As Integer = DT.Columns.Count
                                    Dim vCurrentCol As Integer = 0
                                    For Each Col As DataColumn In DT.Columns
                                        Dim vColName As String = Col.ColumnName
                                        vInsert += vColName
                                        vCurrentCol += 1
                                        If vColumns = vCurrentCol Then
                                            vInsert += ") VALUES ('"
                                        Else
                                            vInsert += ", "
                                        End If
                                    Next
                                    Dim CurrentSubRow As Integer = 0
                                    Dim vTotalSubRows As Integer = DT.Rows.Count
                                    Dim vInsertValue As String = ""
                                    Dim vValue As String = ""

                                    For Each Row As DataRow In DT.Rows
                                        Dim vIndex = Row.Table.Rows.IndexOf(Row)
                                        For i As Integer = 0 To vColumns - 1
                                            vInsertValue += Row(i).ToString
                                            If i = vColumns - 1 Then
                                                vInsertValue += "') "
                                            Else
                                                vInsertValue += "', '"
                                            End If
                                        Next
                                        If InsertData_DataMS(vInsert & vInsertValue, DBID, 518, False) = False Then
                                            IsError = True
                                            Exit Sub
                                        End If
                                        vInsertValue = ""

                                    Next

                                Else
                                    'Bulk update
                                    strSQL = "SELECT * FROM " & vTable
                                    Using DS As DataSet = ReturnDataSet_Data(strSQL, DBID, False)
                                        DT = DS.Tables(0).Copy
                                    End Using


                                    If BulkUpdate_DataMS(DT, DBID, False, vTable) = False Then
                                        IsError = True
                                    End If


                                End If

                            End Sub)

             Await Task.Run(Sub()
                                strSQL = "SET IDENTITY_INSERT " & vTable & " OFF"
                                If InsertData_DataMS(strSQL, DBID, 495, False) = False Then
                                    IsError = True
                                End If
                            End Sub)


         End If


         If IsError = True Then
             LoadingCompleted("Error", "There was an error uploading the data", vImage)
             AppBoxError("There was an error uploading the data")
         Else
             LoadingCompleted("Ready...", vTable & " was successfully saved", vImage)
             AppBoxSuccess(vTable & " was successfully saved")
         End If



     End If

     


 Catch ex As Exception
     EmailError(ex, 60, PageName)
     LoadingCompleted("Error", "There was an internal error", vImage)
 End Try
End Sub
    • 0

相关问题