How To Insert SharePoint List Item To MS SQL Server Using PowerShell

Introduction

 
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)
  1. Windows PowerShell
  2. SharePointPnpPowerShellOnline Module
Please install the SharePointPnpPowerShellOnline module if it’s not already present using the below command.
  1. Install-Module SharePointPnPPowerShellOnline  
SharePoint List sample columns with data
 
How To Insert SharePoint List Item To MS SQL Server Using PowerShell
Micrsoft SQL server Table without data
 
How To Insert SharePoint List Item To MS SQL Server Using PowerShell
 
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="vinit@abc.com" - 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
  1. $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:
  1. [SecureString]$SecurePass = ConvertTo-SecureString $O365ServiceAccountPwd -AsPlainText –Force    
  2. [System.Management.Automation.PSCredential]$PSCredentials = New-Object System.Management.Automation.PSCredential($O365ServiceAccount, $SecurePass)    
  3. 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:
  1. Install-Module -Name SqlServer  
The below snippet will read items from List Items Collection variable $Items and insert the items into SQL table.
  1. #Loop through the items  
  2. foreach($Item in $Items) {  
  3.         $MovedToSQL = $Item["MovedToSQL"]  
  4.         # check  
  5.         if item already moved to SQL  
  6.         if ($MovedToSQL - ne "Yes") {  
  7.             #  
  8.             $ productName = $Item["ProductName"]  
  9.             $ productDescription = $Item["ProductDescription"]  
  10.             $ productCost = $Item["ProductCost"]  
  11.             $ productMake = $Item["ProductMake"]  
  12.             # Insert query  
  13.             for SQL Table  
  14.             $insertquery = "    
  15.             INSERT INTO[$DatabaseName]. [$TableName]  
  16.                 ([ProductName], [ProductDescription], [ProductCost], [ProductMake])  
  17.             VALUES('$productName ''$productDescription ''$productCost''$productMake')  
  18.             GO "     
Invoke SQLcmd command to insert the item into SQL table based on Query generated in the above code snippet:
  1. 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
  1. Set-PnPListItem -List $List -Identity $Item.Id -Values @{ "MovedToSQL"="Yes"}  
Complete Powershell script
  1. #This script to pull the SharePoint Online List data and move into SQL  
  2. #created by Vinit Kumar  
  3. #SQL data base information - variables - Please change  
  4. $MSSQLServerInstanceName = "ContosoServer\InstanceName"  
  5. $DatabaseName = "DBTest"  
  6. $TableName = " Product "  
  7. # SharePoint Variables - Please change  
  8. $O365ServiceAccount = "vinit@abc.com"  
  9. $O365ServiceAccountPwd = "abc@2020"  
  10. $siteURL = "https://abc.sharepoint.com/sites/test"  
  11. $List = " Product "  
  12. #Connect to SharePoint Online[SecureString] $SecurePass = ConvertTo - SecureString $O365ServiceAccountPwd - AsPlainText - Force[System.Management.Automation.PSCredential] $PSCredentials = New - Object System.Management.Automation.PSCredential($O365ServiceAccount, $SecurePass)  
  13. Connect - PnPOnline - Url $siteURL - Credentials $PSCredentials  
  14. #Get Sharepoint List items  
  15. $Items = Get - PnPListItem - List $List  
  16. #Loop through the items  
  17. foreach($Item in $Items) {  
  18.     $MovedToSQL = $Item["MovedToSQL"]  
  19.     # check  
  20.     if item already moved to SQL  
  21.     if ($MovedToSQL - ne "Yes") {  
  22.         #  
  23.         $productName = $Item["ProductName"]  
  24.         $productDescription = $Item["ProductDescription"]  
  25.         $productCost = $Item["ProductCost"]  
  26.         $productMake = $Item["ProductMake"]  
  27.         # Insert query  
  28.         for SQL Table  
  29.         $insertquery = "    
  30.         INSERT INTO[$DatabaseName]. [$TableName]  
  31.             ([ProductName], [ProductDescription], [ProductCost], [ProductMake])  
  32.         VALUES('$productName  , '$productDescription  , '$productCost ''$productMake ')  
  33.         GO "    
  34.         #Invoke SQLcmd to insert the item into SQL table  
  35.         Invoke - SQLcmd - ServerInstance $MSSQLServerInstanceName - query $insertquery - Database $DatabaseName  
  36.         #Update the SharePoint List once item moved and update the Column "MovedToSQL" = "Yes"  
  37.         Set - PnPListItem - List $List - Identity $Item.Id - Values @ {  
  38.             "MovedToSQL" = "Yes"  
  39.         }  
  40.     }  
  41. }   

Result after execution of Script

 
Once the PowerShell has executed we have updated SharePoint List and SQL table as below:
 
SharePoint List Output
 
How To Insert SharePoint List Item To MS SQL Server Using PowerShell
SQL Table Output
 
How To Insert SharePoint List Item To MS SQL Server Using PowerShell
 

Summary

 
In this article, I discussed how we can Insert the SharePoint List item into a Microsoft SQL Table using PowerShell Script. We saw the power of PnP commands which help us to easily read and Update SharePoint Items. Invoke-SQLcmd is used for inserting the rows using Powershell and in the end we saw the output of the SQL Table with inserted records. This article will help report developers who use a SQL server as a reporting database but the business wants to have some report from SharePoint Online Lists. In this case the developer can move SharePoint Online List items as per a Schedule Job to SQL table using this script and generate the report for the business.