<< SQL Server Hosting - HostForLIFEASP.NET :: Quickly Blocking Inbound Connections To SQL Server | SQL Server Hosting - HostForLIFE :: SQL Server Performance Tuning Tips >>

SQL Server Hosting - HostForLIFE :: How To Insert SharePoint List Item To MS SQL Server Using PowerShell?

April 15, 2021 08:49 by author

Peter

This article demonstrates how to Insert the SharePoint Online List item to On-Premise Microsoft SQL Table using PowerShell. It starts with the introduction of the Get-PnPListItem command available in SharePointPnpPowerShellOnline PowerShell module to read List items from SharePoint List. After that, it demonstrates how to create the SQL query and use Invoke-SQLcmd to create rows for each item in SQL Table. In the end, the article discusses how to Update Column MoveToSQL in the SharePoint List item, so that in next run of PowerShell code it does not insert the same items in SQL table.

This PowerShell script can be used as either a one time activity to insert SharePoint List Item to SQL Table or used as a scheduler so it keeps inserting the List items into Microsoft SQL Table.

Pre-requisites (Environment Details)
    Windows PowerShell
    SharePointPnpPowerShellOnline Module

Please install the SharePointPnpPowerShellOnline module if it’s not already present using the below command.

    Install-Module SharePointPnPPowerShellOnline

SharePoint List sample columns with data

Micrsoft SQL server Table without data

Variables Explanations in this Article
    $MSSQLServerInstanceName="ContosoServer\InstanceName" - on-premise SQL Server Instance
    $DatabaseName="DBTest" - on-premise SQL Database Name
    $TableName="Product" - SQL Table Name
    $O365ServiceAccount="[email protected]" - Your Service Account Name
    $O365ServiceAccountPwd="abc@2020" - Your Service Account password
    $siteURL="https://abc.sharepoint.com/sites/test" - SharePoint Site URL from where the item will be read
    $List="Product" - List Name

Here you can see we have provided the password in plain text which you need to provide if you want this PowerShell script to run automatically through Timer Job or Scheduler.

For manual execution please use the Get-Credential command to read the user name and password from the user input.

Read SharePoint List Items
SharePointPnpPowerShellOnline Module of PowerShell has made developers' lives easier. To read the all the list items from a list use the code snippet as below.

Please find all the parameter associated with Get-PnpListItem

    $Items=Get-PnPListItem -List $List

Here $Items variable will hold all the items from the List.

Before reading the list you should be connecting to the SharePoint Site using the below snippet:

    [SecureString]$SecurePass = ConvertTo-SecureString $O365ServiceAccountPwd -AsPlainText –Force
    [System.Management.Automation.PSCredential]$PSCredentials = New-Object System.Management.Automation.PSCredential($O365ServiceAccount, $SecurePass)
    Connect-PnPOnline -Url $siteURL -Credentials $PSCredentials

Create SQL Query and Invoke-SQLcmd

Once the PowerShell Script has read all the items of the list and stores in a Variable, the next step is to read the List item form a SQL query with column values and insert to SQL Table one by one in for loop using Invoke-sqlcmd.

If Invoke-sqlcmd is not found as PowerShell Module , please install the SQL Server PowerShell Module using the command below:

    Install-Module -Name SqlServer

The below snippet will read items from List Items Collection variable $Items and insert the items into SQL table.
    #Loop through the items
    foreach($Item in $Items) {
            $MovedToSQL = $Item["MovedToSQL"]
            # check
            if item already moved to SQL
            if ($MovedToSQL - ne "Yes") {
                #
                $ productName = $Item["ProductName"]
                $ productDescription = $Item["ProductDescription"]
                $ productCost = $Item["ProductCost"]
                $ productMake = $Item["ProductMake"]
                # Insert query
                for SQL Table
                $insertquery = "
                INSERT INTO[$DatabaseName]. [$TableName]
                    ([ProductName], [ProductDescription], [ProductCost], [ProductMake])
                VALUES('$productName ', '$productDescription ', '$productCost', '$productMake')
                GO "

Invoke SQLcmd command to insert the item into SQL table based on Query generated in the above code snippet:
    Invoke-SQLcmd -ServerInstance $MSSQLServerInstanceName -query $insertquery -Database $DatabaseName

Update MoveToSQL Column of SharePoint List

This step is necessary if we want to avoid duplication of the same data in SQL table . Once the Item is inserted into the SQL Table, update “MoveToSQL” Column of the SharePoint List Item with value “Yes” using Set-PnPListItem PowerShell Command.

Below is the code snippet to update the list item
    Set-PnPListItem -List $List -Identity $Item.Id -Values @{ "MovedToSQL"="Yes"}

Complete Powershell script
    #This script to pull the SharePoint Online List data and move into SQL
    #created by Vinit Kumar
    #SQL data base information - variables - Please change
    $MSSQLServerInstanceName = "ContosoServer\InstanceName"
    $DatabaseName = "DBTest"
    $TableName = " Product "
    # SharePoint Variables - Please change
    $O365ServiceAccount = "[email protected]"
    $O365ServiceAccountPwd = "abc@2020"
    $siteURL = "https://abc.sharepoint.com/sites/test"
    $List = " Product "
    #Connect to SharePoint Online[SecureString] $SecurePass = ConvertTo - SecureString $O365ServiceAccountPwd - AsPlainText - Force[System.Management.Automation.PSCredential] $PSCredentials = New - Object System.Management.Automation.PSCredential($O365ServiceAccount, $SecurePass)
    Connect - PnPOnline - Url $siteURL - Credentials $PSCredentials
    #Get Sharepoint List items
    $Items = Get - PnPListItem - List $List
    #Loop through the items
    foreach($Item in $Items) {
        $MovedToSQL = $Item["MovedToSQL"]
        # check
        if item already moved to SQL
        if ($MovedToSQL - ne "Yes") {
            #
            $productName = $Item["ProductName"]
            $productDescription = $Item["ProductDescription"]
            $productCost = $Item["ProductCost"]
            $productMake = $Item["ProductMake"]
            # Insert query
            for SQL Table
            $insertquery = "
            INSERT INTO[$DatabaseName]. [$TableName]
                ([ProductName], [ProductDescription], [ProductCost], [ProductMake])
            VALUES('$productName , '$productDescription , '$productCost ', '$productMake ')
            GO "
            #Invoke SQLcmd to insert the item into SQL table
            Invoke - SQLcmd - ServerInstance $MSSQLServerInstanceName - query $insertquery - Database $DatabaseName
            #Update the SharePoint List once item moved and update the Column "MovedToSQL" = "Yes"
            Set - PnPListItem - List $List - Identity $Item.Id - Values @ {
                "MovedToSQL" = "Yes"
            }
        }
    }

Result after execution of Script
Once the PowerShell has executed we have updated SharePoint List and SQL table as below: