UPDATE a Table with data from a CSV file

[former_SQLA_member1694879]

I know how to import (INSERT) data into a table from a CSV file, but is there any method to update a table?

I was half asleep and made a mess of table in running DB, the customer changed some data so I can't just carry out a restore for that DB. I was, however, able to run a backup copy and export the effected records to CSV - now, if possible, I would like to carry out an UPDATE using that file. Is that possible?

Thank you

[VolkerBarth]

If you have used INSERT to insert the rows, and they have a primary key value that is contained in the CSV file (so the primary key values are not automatically generated during the import), you should be fine to use INSERT...ON EXISTING UPDATE or the MERGE statement to update existing rows.

[former_SQLA_member1694879]

Thanks Volker! The utility we had did the job, just got that sinking feeling when I realized what I had done. Just as well there are backups!

[former_SQLA_member1694879]

Turns out we had a method of a sort in a WPF utility that I forgot I wrote a few years ago

  Private Async Sub DataImportUpdate_Run(sender As Object, e As RoutedEventArgs)
    Try
        Dim CB As ComboCBx = LogicalTreeHelper.FindLogicalNode(MainPopUp, "DataImportUpdate_TablesCB")
        Dim vSelected As String = CB.Text
        Dim vColumns As Integer = 0
        Dim IsSaveError As Boolean = False
        StatusBarLoading("Updating Database... Please wait...")
        BusyGrid.IsBusy = True
        BusyGrid.BusyContent = "Updating Database... Please wait..."



        Await Task.Run(Sub()

                           Using vService As New Service1Client
            strSQL = "SELECT * FROM " & vSelected
            Using DS As DataSet = vService.ReturnDataSet(strSQL, Current_HOA_ID)
                vColumns = DS.Tables(0).Columns.Count
            End Using
        End Using

                       End Sub)

        'Return the primary column name
        Dim vPrimaryColumnName As String = ""
        Dim vPrimaryColumnNumber As Integer = 0
        For vi As Integer = 0 To vColumns - 1
            Dim IsPrimaryCB As CheckBox = LogicalTreeHelper.FindLogicalNode(MainPopUp, "IsPrimaryCB_" & vi)

            Dim ColumnNameTB As TBx = LogicalTreeHelper.FindLogicalNode(MainPopUp, "Label_" & vi)
            Dim vColumnName As String = ColumnNameTB.Text
            Dim ColumnNumberTB As TBx = LogicalTreeHelper.FindLogicalNode(MainPopUp, vColumnName & "_Data")
            Dim vColumnNumber As String = ColumnNumberTB.Text
            If IsPrimaryCB.IsChecked = True Then
                vPrimaryColumnName = vColumnName
                If IsNumeric(vColumnNumber) = True Then
                    vPrimaryColumnNumber = vColumnNumber
                Else
                    AppBoxValidation("The column number for " & vPrimaryColumnName & " is not a valid number")
                End If
            End If
        Next

        ' Dim vRowCount As Integer = 1
        For Each Row As DataRow In MainDT.Rows
            'Pull out the column names
            Using vService As New Service1Client
                strSQL = "UPDATE " & vSelected & " SET "
                For vi As Integer = 0 To vColumns - 1
                    Dim IsSelectedCB As CheckBox = LogicalTreeHelper.FindLogicalNode(MainPopUp, "IsSelectedCB_" & vi)
                    Dim ColumnNameTB As TBx = LogicalTreeHelper.FindLogicalNode(MainPopUp, "Label_" & vi)
                    Dim vColumnName As String = ColumnNameTB.Text
                    Dim ColumnNumberTB As TBx = LogicalTreeHelper.FindLogicalNode(MainPopUp, vColumnName & "_Data")
                    Dim vColumnNumber As String = ColumnNumberTB.Text
                    If IsSelectedCB.IsChecked = True Then
                        strSQL += vColumnName & " = '" & Row("Column_" & vColumnNumber) & "', "
                    End If
                Next
                'Remove the last comma
                strSQL = strSQL.Remove(strSQL.Length - 2)
            End Using

            If IsSaveError = False Then
                Using vService As New Service1Client
                    strSQL += " WHERE " & vPrimaryColumnName & " = " & Row("Column_" & vPrimaryColumnNumber)
                    If vService.InsertDataHOA(strSQL, "PopupModals 8818", Current_HOA_ID) = False Then
                        IsSaveError = True
                    End If
                End Using
            End If
            ' vRowCount += 1
        Next



        If IsSaveError = True Then
            StatusBarLoaded("There was an error at " & strSQL)
            ServerError()
        Else
            StatusBarLoaded("The import completed successfully...")
        End If


    Catch ex As Exception
        EmailError(ex)
    Finally
        BusyGrid.IsBusy = False
    End Try
End Sub