Tags: Relational databasesDesktopCommand line
“I built our asset tracker in Microsoft Lists, and now finance want it in SQL Server.” If that is your week, you have probably already searched for “Microsoft Lists to SQL Server” and found Commerce Server documentation from the early 2000s, which has nothing to do with the Lists app. The vendor pages that do use the name tend to skip the one fact that makes the job easy: the list you made in the Lists app is a SharePoint list.
This post covers what that means, the one thing to check before you choose a route, and the three routes into SQL Server. We build SPSync, which replicates SharePoint lists into SQL Server and Azure SQL, so one of the routes is ours. Microsoft’s limits are linked to Microsoft’s own pages.
Is a Microsoft Lists list a SharePoint list?
Yes. Microsoft Lists is an app over SharePoint lists, and Microsoft’s guide to SharePoint lists in Power Apps says you can always find a list in both Microsoft Lists and SharePoint. Open a list in the Lists app, then open the site it was saved to, and the same list is under Site contents with the same columns and rows.
So the questions people ask about the Lists app are SharePoint questions underneath. Its columns and permissions are SharePoint’s own, and any tool that reads a SharePoint list through Microsoft Graph reads a Lists list in the same way.
Team site or My lists: check where your list lives
When you create a list in the Lists app, Microsoft Support’s Create a list instructions show a Save to choice: My lists, or one of your SharePoint sites. That choice matters more than anything else in this post.
A list saved to a site lives on that site, next to the team’s documents, and you find it under the site’s Site contents. A list saved to My lists is a personal list. It is stored in the creator’s OneDrive rather than on a team site, and you find it in the Lists app under My lists. Microsoft treats the two differently in other places too. A SharePoint administrator can turn off personal list creation for the whole tenant, after which every new list has to be saved to a site, and Databricks’ SharePoint connector does not ingest lists from OneDrive at all.
To check yours, open the list and look at the address bar. A site list has the site’s
address in it. A personal list has your OneDrive address, the one with -my.sharepoint.com
and /personal/ in it.
What Sites.Selected means for each location
An unattended copy into SQL Server needs an app that can read the list on a schedule, with nobody signed in. The narrowest grant for that is Sites.Selected: an administrator consents to the app, then grants it access to named sites, and it can read those and nothing else.
For a team list that is one grant, on the site the team already works in. For a personal list, the grant would have to name one person’s OneDrive, and the list would be tied to that person’s account and not to the team. If they leave, their OneDrive goes through Microsoft’s OneDrive account retention process, and the list goes with it.
SPSync reads lists on a SharePoint site, either with your own delegated sign-in or with a Sites.Selected grant. We have not tested it against a list under My lists, so we do not claim that it works there. If the data matters to more than one person, save the list to a team site first. The same Create a list page notes that creating a list from an existing list copies the columns but none of the data. For a personal list that already holds rows, export it to CSV and create the site list from that file, using the From CSV option on the same page.
Can you use Microsoft Lists as a database?
For editing rows, sharing them and building a simple form, it works well. The limitations show once the list grows or the questions get harder. A view that filters or sorts on an unindexed column is refused once the list passes the 5,000-item list view threshold, even though a list can hold up to 30 million items. There are no joins across lists beyond the lookup columns, which SharePoint caps, and totalling a column across a large list means a report that reads every row.
The usual answer is to keep the list for the people editing it and add a SQL copy for everything else. The SharePoint and Microsoft Lists limits cheat sheet has each limit with its Microsoft source.
Three routes from Microsoft Lists to SQL Server
Every route below works on a list saved to a site, because each one reads a SharePoint list.
The first is an export. The list’s Export button gives you a CSV or an Excel file, and SQL Server Management Studio’s import wizard or a bulk insert loads it. For a copy you need once, that is the right answer and takes an afternoon. It forgets deleted rows, carries no schema and has to be run again by hand next month.
The second is a Power Automate flow, triggered when an item is created or modified, that writes the row to SQL Server. It keeps up with edits on a small list. On a large one, the Get items action pages up to 100,000 items on every performance profile above Low, deleted items need a second flow, and a new column means editing the flow. SPSync vs Power Automate sets out which jobs to keep in a flow. A PnP PowerShell script on a schedule is the developer’s version of the same idea, compared in SPSync vs PnP PowerShell and custom scripts.
The third is replication. SPSync reads the list with Microsoft Graph delta queries, which page through it without touching a view, so a list past 5,000 items copies like a small one. Each list becomes a table keyed on the SharePoint item id, lookups become id columns, and multi-value columns become child tables. Later runs read only what changed, and a reconcile, weekly by default, checks the table still matches the list. A row deleted in the list stays in SQL Server with a deleted flag. The copy is one way: SharePoint stays the system of record and nothing written in SQL Server goes back to the list. Seven steps from a SharePoint list to a SQL Server table walks through the setup in the desktop app and on the command line.
Microsoft Lists in Power BI, from the SQL copy
Power BI’s SharePoint Online list connector reads a Lists list directly, which is fine while the list is small. As it grows, refreshes slow down and lookups come back as ids. A report built on the SQL copy reads a table instead, and SPSync can generate reporting views, pre-joined and flattened, that Power BI reads with import or DirectQuery. SharePoint list to SQL Server for a faster Power BI report covers that setup, and why Power BI refreshes on SharePoint lists slow down covers the connector itself.
Where to go next for SQL Server and Azure SQL
The SharePoint Online and Microsoft Lists to SQL Server page describes the destination, its schema and its connection strings, and SharePoint Online lists in Azure SQL does the same for Azure SQL Database and Managed Instance. If you are weighing a flow or a script against replication, what it costs to keep a SharePoint export script running sets out the upkeep of each.
Check where your list is saved, move it to a team site if it is under My lists, then pick the route that matches how often the copy has to be current. Book a call if you would like to talk it through, and we will set up a trial against your own tenant on the same call.