How to compare csv file with sql database using PowerShell-CodePudding

I've latest data in my CSV file, I'm trying to compare to my SQL Server Database Example1 table that contain old data using the EmpId and Name columns. If there is new data in the csv file then update Example1 table and insert only the new data into Example2 table.

I'm beginner and I'm stuck with the logic part so I would be really appreciated if I can get any help or suggestion. Any guidance would be really appreciated.

My CSV file, Example1 and Example2 database tables look like this

EmpId    Name           Hold
324   John Smith     Yes
432   Tim Cook       Yes

        $csv_file = "C:\scripts\user-info.csv"
        
        $csvImport = import-csv $csv_file

        foreach ($i in $csvImport){
              $EmpId = $i.EmpId
              $Name = $i.Name
              $Hold = $i.Hold

        }


        $Server = 'Server'
        $query = "SELECT EmpId,Name FROM Example1"
        $Database = 'DatabaseName'
        $result = Invoke-Sqlcmd -Query $query -ServerInstance $Server -Database $Database  | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors

CodePudding user response：

Since I'm always struggling with the Compare-Object cmdlet, here's an option to 'manually' compare the differences

Assuming the EmpId field is unique:

$Server   = 'Server'
$query    = "SELECT EmpId,Name FROM Example1"
$Database = 'DatabaseName'
$dbData   = Invoke-Sqlcmd -Query $query -ServerInstance $Server -Database $Database | 
            Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors
$csvData  = Import-Csv -Path 'C:\scripts\user-info.csv'

# iterate over the data rows in the CSV file
$result = foreach ($item in $csvData) {
    $dbRecord = @($dbData | Where-Object { $_.EmpId -eq $item.EmpId })
    if ($dbRecord.Count -eq 0) {
        # this is a new user to be added in the database

        # you can perform the INSERT statement right here or do that
        # afterwards by iterating the $result array and checking for items
        # where the Status field is 'New'.
        # for the latter, output an object:
        $item | Select-Object *, @{Name = 'Status'; Expression = {'New'}}
    }
    else {
        $dbRecord | ForEach-Object {
            if ($_.Name -ne $item.Name -or $_.Hold -ne $item.Hold) {
                # this is an existing record that needs updating. output what is in the CSV

                # you can perform the UPDATE statement right here or do that
                # afterwards by iterating the $result array and checking for items
                # where the Status field is 'Update'.
                # for the latter, output an object:
                $item | Select-Object *, @{Name = 'Status'; Expression = {'Update'}}
            }
        }
    }
}

Now in variable $result you should have an array of all differences including a status property from which you know whether to insert a new record or to update an existing one.