Get data from SharePoint List and SQL Table - compare them - PowerShell

Question

I want to make a script which will get data from SharePoint list (from a specific column) and also get data from SQL table (from a specific column) and compare them. If the comparison value is true then get another specific column value from SQL table and set it to SharePoint list.

I will compare identification number value from SharePoint list with identification number from SQL table, if value will be true then another column that falls under the identification number in SQL table will be added to SharePoint list in compared value (identification number)

This need to be done with PowerShell.

Answer 1

It's very unclear what exactly you are trying to do, but you can start from this:

$server = "YourDatabaseServer"
$database = "YourDatabaseName"
$timeout = 10
$query = "SELECT Id as [ItemId], AnotherColumnFromSql
    FROM yourTable"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $server,$database,$timeout
$connection.ConnectionString=$connectionString
$connection.Open()
    $command = New-Object System.Data.SqlClient.SqlCommand($query,$connection)
    $command.CommandTimeout = $timeout
    $dataSet = New-Object System.Data.DataSet
    $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
    [void]$dataAdapter.Fill($dataSet)
$connection.Close()
$connection.Dispose()

$web = Get-SPWeb "https://yoursiteurl"
$list = $web.GetList("https://yoursiteurl/lists/yourlist")

foreach ($row in $dataSet.Tables[0].Rows) {
    try {
        $item = $list.GetItemById($row.ItemId)
        if ($item) {
            $item["AnotherSharePointColumn"] = $row.AnotherColumnFromSql
            $item.Update()
        }
    } catch {
        Write-Host "Item with Id $($row.ItemId) does not exist!"
        Continue
    } 
}