Do you need to keep data in SharePoint Lists in sync with source data on a local server? Microsoft provides a Data Management Gateway giving access via Power Platform, however this requires premium licensing, that sometimes is hard to justify. Another option to explore is using PowerShell to keep the data up to date.

Using this method can reduce the need for premium data connectors and licensing because PowerApps using SharePoint Lists as a data source do not require premium licenses. There can be challenges, especially when it comes to working with big sets of data.

Using PNP PowerShell you can create, update and delete SharePoint List items. Using Add-PNPListItem to create new List items is a great place to start when  creating a small number of items. If you need to create or update large numbers of items, use the New-PNPBatch command to accelerate the bulk update process.

Creating List items without using New-PNPBatch  results in large numbers of API calls to SharePoint Online and can be very slow, taking several seconds per item when you work with large lists. Batching the updates reduces to very small number of API calls, resulting in much faster performance. I have seen performance of 100 items per second in a large list (over 30000 items).

Good news, you can easily add the Batch functionality to your existing code if you are using Add-PNPListItem or Set-PNPListItem. Both of these commands support -Batch parameter.

In this example, I read a CSV file with 3 columns (Title, Type, Location) and create a List item for each line in the file.

# Read CSV file
$documents = Import-CSV c:\test\datafile.csv -Header Title,Type,Location

$batch = New-PnPBatch
 
foreach($item in $databaseitems) 
{
    Add-PnPListItem -List "MyList" -Values @{"Title" = $item.Title; "Type" = $item.type; "Location" = $item.location } -Batch $batch
}
 
# Execute the batch all at once
Invoke-PnPBatch -Batch $batch

A few other things to consider when pushing data from SQL to SharePoint:

• PowerShell can execute a SQL Query (Invoke-SQLcmd).
• Perform preprocessing in SQL, it is generally faster.
• Try to work with the smallest set of updates by processing deltas only.
• Deleting and adding a List item will create new Item ID’s in SharePoint.
• Delete operations cannot be done with in ‘Batch’ mode.

There are alternative methods for updating data including using SQL Server Integration Services or other integration tools using the SharePoint List RestAPI or OData connectors.

SharePoint might not be the right data source for every requirement. There are alternatives including Dataverse for Teams (no additional licensing cost), and various premium data sources including SQL Server, Azure SQL Database, Dataverse (Enterprise version). Note that premium data sources require a license for each user of the PowerApp.

To use PnP PowerShell modules you will need to install the PnP PowerShell modules on the PC or Server running PowerShell.