PowerShell - Read data from SQL Table and Insert in to SharePoint List

PowerShell - Read data from SQL Table and Insert in to SharePoint List:-

#Gets the Powershell snapin
$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
#Checking the snapin
if ($snapin -eq $null)
{

Write-Host "Loading SharePoint Powershell Snapin" -ForegroundColor Green
$snapin = Add-PSSnapin "Microsoft.Sharepoint.Powershell"

Write-Host $snapin
}

#Creating sql connection
WRite-HOst "Moving XREF information to Sharepoint list"
$con = New-Object System.Data.SqlClient.SqlConnection
#Providing sql connection string
    $con.ConnectionString = "Server=<Server Name>;Database<Database Name>;Integrated Security=false;user id=<User Id>;password=<Password>";
    # Opening the connection
    $con.open()
    #Creating new sql command
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    #Providing query to command
    $cmd.CommandText = "select lm.LOAN as Loan,eg.XREF as Xref from LNMAST LM,egeneral eg WHERE lm.LOAN = eg.LOAN and XREF is not null and XREF != '' order by lm.LOAN asc"
    #Providing connection to command
    $cmd.Connection = $con
 
    # Create SqlDataAdapter object and set the command
    $da = New-Object System.Data.SqlClient.SqlDataAdapter
    $da.SelectCommand = $cmd

    # Create and fill the DataSet object
    $LMSDataTable = New-Object System.Data.DataTable
    $da.Fill($LMSDataTable)
    #Closing the sql connection
    if($con -ne $null)
    {
        $con.close();
    }
 
    [Microsoft.SharePoint.SPSecurity]::RunWithElevatedPrivileges(

    {

 #Getting the web site
    $DebtWeb=Get-SPWeb "http://<Machine Name>:8800/<Subsite>/"
    #Getting the List
    $mylist = $DebtWeb.Lists["Mapping"] # List Name
 
    $Items = $mylist.GetItems()
    $Items | ForEach-Object{$mylist.GetItemById($_.Id).Delete()}
    $mylist.Update()
 
    #looping lms datatable
    foreach ($row in $LMSDataTable.Rows) {
 
 
    #adding new item in list
 
    $newItem = $mylist.Items.Add()
    #assigning values to list columns
    $newItem["Title"] = $($row["Loan"])
    $newItem["LoanNumber"] = $($row["Loan"])
    $newItem["XrefNumber"] = $($row["Xref"])
    #updating list item
    $newItem.Update()
 


    }

   }

)

 

    #End
    WRite-HOst "XREF information moved to Sharepoint list"