VBA code.... insert query for new new records update for existing records

Dim conn As ADODB.Connection, rst As ADODB.Recordset, myarray
Dim rowcount As Long, updatequery As String
Dim apprisaldate1 As String, apprisaldate2 As String, apprisaldate3 As String, apprisaldate4 As String, apprisaldate5 As String, apprisaldate6 As String, apprisaldate7 As String, apprisaldate8 As String, apprisaldate9 As String, apprisaldate10 As String
Dim lastsalary1 As String, lastsalary2 As String, lastsalary3 As String, lastsalary4 As String, lastsalary5 As String, lastsalary6 As String, lastsalary7 As String, lastsalary8 As String, lastsalary9 As String, lastsalary10 As String
Dim revisedsalary1 As String, revisedsalary2 As String, revisedsalary3 As String, revisedsalary4 As String, revisedsalary5 As String, revisedsalary6 As String, revisedsalary7 As String, revisedsalary8 As String, revisedsalary9 As String, revisedsalary10 As String
Sub insertecord()
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets(1).AutoFilterMode = False
   ' On Error GoTo errorHandler
    rowcount = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
   
    Set conn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    conn.ConnectionString = "Data Source=HRAutomation;Initial Catalog=KoenigDb;uid=sa;pwd=Pa$$w0rd;"
    conn.Open
    'rst.Open "EmpAppraisal", conn, adOpenKeyset, adLockBatchOptimistic, adCmdTable
    For i = 2 To rowcount
    'MsgBox "Emp Id" & ThisWorkbook.Sheets(1).Range("A" & i)
   
    Set rst = conn.Execute("Select * from EmpAppraisal where [Employee ID]='" & ThisWorkbook.Sheets(1).Range("A" & i) & "';")
    If Not rst.EOF Then
        myarray = rst.GetRows
        If UBound(myarray) > 0 Then
            apprisaldate1 = ThisWorkbook.Sheets(1).Range("B" & i)
            apprisaldate2 = ThisWorkbook.Sheets(1).Range("E" & i)
            apprisaldate3 = ThisWorkbook.Sheets(1).Range("H" & i)
            apprisaldate4 = ThisWorkbook.Sheets(1).Range("K" & i)
            apprisaldate5 = ThisWorkbook.Sheets(1).Range("N" & i)
            apprisaldate6 = ThisWorkbook.Sheets(1).Range("Q" & i)
            apprisaldate7 = ThisWorkbook.Sheets(1).Range("T" & i)
            apprisaldate8 = ThisWorkbook.Sheets(1).Range("W" & i)
            apprisaldate9 = ThisWorkbook.Sheets(1).Range("Z" & i)
            apprisaldate10 = ThisWorkbook.Sheets(1).Range("AC" & i)
           
            lastsalary1 = ThisWorkbook.Sheets(1).Range("C" & i)
            lastsalary2 = ThisWorkbook.Sheets(1).Range("F" & i)
            lastsalary3 = ThisWorkbook.Sheets(1).Range("I" & i)
            lastsalary4 = ThisWorkbook.Sheets(1).Range("L" & i)
            lastsalary5 = ThisWorkbook.Sheets(1).Range("O" & i)
            lastsalary6 = ThisWorkbook.Sheets(1).Range("R" & i)
            lastsalary7 = ThisWorkbook.Sheets(1).Range("U" & i)
            lastsalary8 = ThisWorkbook.Sheets(1).Range("X" & i)
            lastsalary9 = ThisWorkbook.Sheets(1).Range("AA" & i)
            lastsalary10 = ThisWorkbook.Sheets(1).Range("AD" & i)
           
           
            revisedsalary1 = ThisWorkbook.Sheets(1).Range("D" & i)
            revisedsalary2 = ThisWorkbook.Sheets(1).Range("G" & i)
            revisedsalary3 = ThisWorkbook.Sheets(1).Range("J" & i)
            revisedsalary4 = ThisWorkbook.Sheets(1).Range("M" & i)
            revisedsalary5 = ThisWorkbook.Sheets(1).Range("P" & i)
            revisedsalary6 = ThisWorkbook.Sheets(1).Range("S" & i)
            revisedsalary7 = ThisWorkbook.Sheets(1).Range("V" & i)
            revisedsalary8 = ThisWorkbook.Sheets(1).Range("Y" & i)
            revisedsalary9 = ThisWorkbook.Sheets(1).Range("AB" & i)
            revisedsalary10 = ThisWorkbook.Sheets(1).Range("AE" & i)
            updatequery = "Update EmpAppraisal set [Appraisal/Increment Date1]='" & apprisaldate1 & "',[Last Salary1]='" & lastsalary1 & "',[Revised Salary1]='" & revisedsalary1 & "',[Appraisal/Increment Date2]='" & apprisaldate2 & "',[Last Salary2]='" & lastsalary2 & "',[Revised Salary2]='" & revisedsalary2 & "',[Appraisal/Increment Date3]='" & apprisaldate3 & "',[Last Salary3]='" & lastsalary3 & "',[Revised Salary3]='" & revisedsalary3 & "',[Appraisal/Increment Date4]='" & apprisaldate4 & "',[Last Salary4]='" & lastsalary4 & "',[Revised Salary4]='" & revisedsalary4 & "',[Appraisal/increment Date5]='" & apprisaldate5 & "',[Last Salary5]='" & lastsalary5 & "',[Revised Salary5]='" & revisedsalary5 & "',[Appraisal/increment Date6]='" & apprisaldate6 & "',[Last Salary6]='" & lastsalary6 & "',[Revised Salary6]='" & revisedsalary6 & "',[Appraisal/increment Date7]='" & apprisaldate7 & "',[Last Salary7]='" & lastsalary7 & "',[Revised Salary7]='" & revisedsalary7 & "',[Appraisal/increment Date8]='" & apprisaldate8 _
& "',[Last Salary8]='" & lastsalary8 & "',[Revised Salary8]='" & revisedsalary8 & "',[Appraisal/increment Date9]='" & apprisaldate9 & "',[Last Salary9]='" & lastsalary9 & "',[Revised Salary9]='" & revisedsalary9 & "',[Appraisal/increment Date10]='" & apprisaldate10 & "',[Last Salary10]='" & lastsalary10 & "',[Revised Salary10]='" & revisedsalary10 & "' where [Employee ID]='" & ThisWorkbook.Sheets(1).Range("A" & i) & "';"
            conn.Execute (updatequery)
        Else
            apprisaldate1 = ThisWorkbook.Sheets(1).Range("B" & i)
            apprisaldate2 = ThisWorkbook.Sheets(1).Range("E" & i)
            apprisaldate3 = ThisWorkbook.Sheets(1).Range("H" & i)
            apprisaldate4 = ThisWorkbook.Sheets(1).Range("K" & i)
            apprisaldate5 = ThisWorkbook.Sheets(1).Range("N" & i)
            apprisaldate6 = ThisWorkbook.Sheets(1).Range("Q" & i)
            apprisaldate7 = ThisWorkbook.Sheets(1).Range("T" & i)
            apprisaldate8 = ThisWorkbook.Sheets(1).Range("W" & i)
            apprisaldate9 = ThisWorkbook.Sheets(1).Range("Z" & i)
            apprisaldate10 = ThisWorkbook.Sheets(1).Range("AC" & i)
           
            lastsalary1 = ThisWorkbook.Sheets(1).Range("C" & i)
            lastsalary2 = ThisWorkbook.Sheets(1).Range("F" & i)
            lastsalary3 = ThisWorkbook.Sheets(1).Range("I" & i)
            lastsalary4 = ThisWorkbook.Sheets(1).Range("L" & i)
            lastsalary5 = ThisWorkbook.Sheets(1).Range("O" & i)
            lastsalary6 = ThisWorkbook.Sheets(1).Range("R" & i)
            lastsalary7 = ThisWorkbook.Sheets(1).Range("U" & i)
            lastsalary8 = ThisWorkbook.Sheets(1).Range("X" & i)
            lastsalary9 = ThisWorkbook.Sheets(1).Range("AA" & i)
            lastsalary10 = ThisWorkbook.Sheets(1).Range("AD" & i)
           
           
            revisedsalary1 = ThisWorkbook.Sheets(1).Range("D" & i)
            revisedsalary2 = ThisWorkbook.Sheets(1).Range("G" & i)
            revisedsalary3 = ThisWorkbook.Sheets(1).Range("J" & i)
            revisedsalary4 = ThisWorkbook.Sheets(1).Range("M" & i)
            revisedsalary5 = ThisWorkbook.Sheets(1).Range("P" & i)
            revisedsalary6 = ThisWorkbook.Sheets(1).Range("S" & i)
            revisedsalary7 = ThisWorkbook.Sheets(1).Range("V" & i)
            revisedsalary8 = ThisWorkbook.Sheets(1).Range("Y" & i)
            revisedsalary9 = ThisWorkbook.Sheets(1).Range("AB" & i)
            revisedsalary10 = ThisWorkbook.Sheets(1).Range("AE" & i)
           
           
insertquery = "insert EmpAppraisal([Employee ID], [Appraisal/Increment Date1],[Last Salary1],[Revised Salary1],[Appraisal/Increment Date2],[Last Salary2],[Revised Salary2],[Appraisal/Increment Date3],[Last Salary3],[Revised Salary3],[Appraisal/Increment Date4],[Last Salary4],[Revised Salary4],[Appraisal/increment Date5],[Last Salary5],[Revised Salary5],[Appraisal/increment Date6],[Last Salary6],[Revised Salary6],[Appraisal/increment Date7],[Last Salary7],[Revised Salary7],[Appraisal/increment Date8],[Last Salary8],[Revised Salary8],[Appraisal/increment Date9],[Last Salary9],[Revised Salary9],[Appraisal/increment Date10],[Last Salary10],[Revised Salary10])" _
& "values('" & empId & "','" & apprisaldate1 & "','" & lastsalary1 & "','" & revisedsalary1 & "','" & apprisaldate2 & "','" & lastsalary2 & "','" & revisedsalary2 & "','" & apprisaldate3 & "','" & lastsalary3 & "','" & revisedsalary3 & "','" & apprisaldate4 & "','" & lastsalary4 & "','" & revisedsalary4 & "','" & apprisaldate5 & "','" & lastsalary5 & "','" & revisedsalary5 & "','" & apprisaldate6 & "'," _
& "'" & lastsalary6 & "','" & revisedsalary6 & "','" & apprisaldate7 & "','" & lastsalary7 & "','" & revisedsalary7 & "','" & apprisaldate8 _
& "','" & lastsalary8 & "','" & revisedsalary8 & "','" & apprisaldate9 & "','" & lastsalary9 & "','" & revisedsalary9 & "','" & apprisaldate10 & "','" & lastsalary10 & "','" & revisedsalary10 & "');"
            conn.Execute (insertquery)
            'ThisWorkbook.Sheets(1).Range("B4") = insertquery
        End If
    Else
            empId = ThisWorkbook.Sheets(1).Range("A" & i)
            apprisaldate1 = ThisWorkbook.Sheets(1).Range("B" & i)
            apprisaldate2 = ThisWorkbook.Sheets(1).Range("E" & i)
            apprisaldate3 = ThisWorkbook.Sheets(1).Range("H" & i)
            apprisaldate4 = ThisWorkbook.Sheets(1).Range("K" & i)
            apprisaldate5 = ThisWorkbook.Sheets(1).Range("N" & i)
            apprisaldate6 = ThisWorkbook.Sheets(1).Range("Q" & i)
            apprisaldate7 = ThisWorkbook.Sheets(1).Range("T" & i)
            apprisaldate8 = ThisWorkbook.Sheets(1).Range("W" & i)
            apprisaldate9 = ThisWorkbook.Sheets(1).Range("Z" & i)
            apprisaldate10 = ThisWorkbook.Sheets(1).Range("AC" & i)
           
            lastsalary1 = ThisWorkbook.Sheets(1).Range("C" & i)
            lastsalary2 = ThisWorkbook.Sheets(1).Range("F" & i)
            lastsalary3 = ThisWorkbook.Sheets(1).Range("I" & i)
            lastsalary4 = ThisWorkbook.Sheets(1).Range("L" & i)
            lastsalary5 = ThisWorkbook.Sheets(1).Range("O" & i)
            lastsalary6 = ThisWorkbook.Sheets(1).Range("R" & i)
            lastsalary7 = ThisWorkbook.Sheets(1).Range("U" & i)
            lastsalary8 = ThisWorkbook.Sheets(1).Range("X" & i)
            lastsalary9 = ThisWorkbook.Sheets(1).Range("AA" & i)
            lastsalary10 = ThisWorkbook.Sheets(1).Range("AD" & i)
           
           
            revisedsalary1 = ThisWorkbook.Sheets(1).Range("D" & i)
            revisedsalary2 = ThisWorkbook.Sheets(1).Range("G" & i)
            revisedsalary3 = ThisWorkbook.Sheets(1).Range("J" & i)
            revisedsalary4 = ThisWorkbook.Sheets(1).Range("M" & i)
            revisedsalary5 = ThisWorkbook.Sheets(1).Range("P" & i)
            revisedsalary6 = ThisWorkbook.Sheets(1).Range("S" & i)
            revisedsalary7 = ThisWorkbook.Sheets(1).Range("V" & i)
            revisedsalary8 = ThisWorkbook.Sheets(1).Range("Y" & i)
            revisedsalary9 = ThisWorkbook.Sheets(1).Range("AB" & i)
            revisedsalary10 = ThisWorkbook.Sheets(1).Range("AE" & i)
           
           
insertquery = "insert EmpAppraisal([Employee ID], [Appraisal/Increment Date1],[Last Salary1],[Revised Salary1],[Appraisal/Increment Date2],[Last Salary2],[Revised Salary2],[Appraisal/Increment Date3],[Last Salary3],[Revised Salary3],[Appraisal/Increment Date4],[Last Salary4],[Revised Salary4],[Appraisal/increment Date5],[Last Salary5],[Revised Salary5],[Appraisal/increment Date6],[Last Salary6],[Revised Salary6],[Appraisal/increment Date7],[Last Salary7],[Revised Salary7],[Appraisal/increment Date8],[Last Salary8],[Revised Salary8],[Appraisal/increment Date9],[Last Salary9],[Revised Salary9],[Appraisal/increment Date10],[Last Salary10],[Revised Salary10])" _
& "values('" & empId & "','" & apprisaldate1 & "','" & lastsalary1 & "','" & revisedsalary1 & "','" & apprisaldate2 & "','" & lastsalary2 & "','" & revisedsalary2 & "','" & apprisaldate3 & "','" & lastsalary3 & "','" & revisedsalary3 & "','" & apprisaldate4 & "','" & lastsalary4 & "','" & revisedsalary4 & "','" & apprisaldate5 & "','" & lastsalary5 & "','" & revisedsalary5 & "','" & apprisaldate6 & "'," _
& "'" & lastsalary6 & "','" & revisedsalary6 & "','" & apprisaldate7 & "','" & lastsalary7 & "','" & revisedsalary7 & "','" & apprisaldate8 _
& "','" & lastsalary8 & "','" & revisedsalary8 & "','" & apprisaldate9 & "','" & lastsalary9 & "','" & revisedsalary9 & "','" & apprisaldate10 & "','" & lastsalary10 & "','" & revisedsalary10 & "');"
            'ThisWorkbook.Sheets(1).Range("B4") = insertquery
            conn.Execute (insertquery)
           
   
   
    End If
  
   
       
    Next
     
           
           
       
        'End If
 'Next
End Sub


Download File