User Case: Can I Import SharePoint List to SQL Server?
I'm using Oracle SQL Developer as a tool and I want to import data from a SharePoint list to my SQL server. I'm wondering if there is any convenient way to do this? In Access, this task is very simple, but unfortunately I need to use SQL Server. Any suggestions? Thanks in advance.
- Question from https://stackoverflow.com/
Why Import Data from SharePoint to SQL Server?
SharePoint provides us with a great way to capture and manage data, but business data stored in SharePoint On-Premise and SharePoint Online lists and libraries are starting to grow exponentially. This usually means we need to report on this data. SharePoint doesn't always make it easy for us!
We could install reporting services into a SharePoint site, but still, it's not easy to report on the data. Since the data is not stored relationally, we found no easy way to cross-reference the data, which meant we had to create a dataset for each query and link them using the "Lookup" and "LookupSet" methods. This method is not ideal!
It would be great if we could export SharePoint data to normalized SQL Server tables and then use the power of SQL Reporting Services to generate our reports using this relational model.
It can give us more flexibility when working with SQL Server databases; We can also manipulate our data using the power and efficiency provided by the SQL Server database engine.
However, moving data between two different accounts is not an easy task. This article will give you 6 feasible methods for your reference.
Best Ways to Import Data from SharePoint to SQL Server
The easiest and fastest way to migrate from SharePoint to SQL server is to use MultCloud. As a free cloud file manager, MultCloud supports direct cross-cloud file transfer and backup without downloading, uploading, switching accounts, etc., and supports the creation of inter-cloud synchronization relationships. Here you will learn how it works.
Step 1. Visit MultCloud to create a free account or get a temporary account to test it out by clicking the "Try without signing up" option.
Step 2. Log in to MultCloud, click "Add Cloud" to select SharePoint and go to the next step. Follow the instructions to grant MultCloud access to the account you want to connect. Then add a SQL server, such as MySQL, in the same way.
In response to the different user needs, MultCloud provides users with different solutions. For users with both SharePoint and SQL server accounts, the following features are available when data movement is required.
Option 1: Use "Copy to" to Copy Files from SharePoint
Once the integration of SharePoint and MySQL is complete, you can use "Copy to" to migrate files from SharePoint to MySQL. Steps: Open your SharePoint account, select the file you want to move to MySQL as the source, then click the "Copy to" option, and select a directory under MySQL as the destination path. And click the "OK" button.
Note: "Multi-select" is an option that allows you to select multiple destinations. After checking this option, you can select multiple cloud paths.
Option 2: Import Data from SharePoint to SQL Server via Cloud Transfer
If you want to move all files under SharePoint to your MySQL account, you can click "Cloud Transfer", select the folder in your SharePoint site as the source, select the directory under your MySQL as the destination path, and click "Transfer Now", the transfer process will begin.
- If you want MultCloud to run the transfer task automatically, you can set a specific time in "Schedule".
- In "Options", you can also set the transfer mode, file filtering and email notification.
Common Ways to Import Data from SharePoint to SQL Server
Using SQL Server as a backup of critical business data provides an essential safety net against loss. Backing up data to SQL Server makes it easier for business users to connect that data with reporting, analytics, and more. In addition to the more convenient data transmission methods above, you can also use the following 4 common methods.
1. Directly access the SharePoint database to export SharePoint data
- Microsoft does not support direct access to SharePoint content databases at all.
- Architecture may change after SharePoint patches and service packs.
- It can cause performance issues with your SharePoint forms.
- If your query is wrong, your report is most likely incorrect too!
2. Export SharePoint list into SQL server using CSOM and PowerShell script
This option provides us with a relational database that we can script to import. We were able to use the SharePoint object model and PowerShell to retrieve SharePoint lists, interrogate fields and dynamically create SQL server tables before fetching the data, translating the data and loading it into a SQL server database.
- Slow and complex setup.
- A lot of work in creating scripts and adjusting them.
- Need to manage database schema generation and mapping.
3. SQL Server Integration Services (SSIS) and the SharePoint List Adapter component for exporting SharePoint data
You can click here for detailed steps to perform this task.
- The whole process is slow.
- The situation is very complex.
- Setting up this task takes a long time.
- Changing the list schema requires changes to the SSIS package and SQL schema.
- Cannot use SharePoint 2013/2016 and SharePoint Online.
4. Export to Excel and then load into SQL server
You can export the contents of a SharePoint list, survey results, or document library to an Excel spreadsheet. The export process exports only the columns and rows contained in the current view of the list. If none of the views contain the data you want to export, you must create a new view to suit your needs. Alternatively, you can select one of the existing views, export the list to a spreadsheet, and delete unwanted data.
Step 1. Action > Export to Spreadsheet. Then save the Excel file.
Step 2. Open your SQL database using SQL Management Studio. Right-click your database > Tasks > Import Data.
Step 3. Select your Excel file as the source and a specific table as the destination.
Step 4. Fill in all other required options. Then click finish.
As you can see, MultCloud can easily help move files from SharePoint to SQL server without having to download apps, download and upload, which can save a lot of effort and time. If you want to try other ways, the 4 ways to transfer data from SharePoint to SQL provided in this article can also meet your needs.
In the process of using MultCloud, in addition to the above functions, you can also use other functions, such as "Cloud Sync" and "Remote Upload". The "Cloud Sync" allows you to sync data between two accounts in real time. The "Remote Upload" function supports downloading files to the corresponding cloud through torrent files or magnet links, such as downloading torrent files directly to MEGA cloud.