We have one site in which external content types are set up and data is stored in SQL Server On premise. Now we want that site to be migrated to SPO. What is the best way to have this set up in SPO? Is it moving to Azure SQL, setting up CSV in shared folder or any other approach which will be less costly and less complex too?

  • How data is flowing in current / on prem environment ? Is Data is flowing from SP to SQL ? Apr 6, 2022 at 11:41
  • @RajatSahani Yes data is flowing from SharePoint to SQL and some columns are also showing data from SQL which is mapped to columns in SharePoint
    – Adarsh
    Apr 7, 2022 at 12:32

2 Answers 2


Azure SQL will be good choice.

Azure SQL <----> Web API <------> SharePoint Online (SPO)


This approach will have an issue with relational fields (if any) and column types (is it safe to assume all column types are string?). A simpler approach would be to create a view that resolves all the relational fields and then write that view out to a csv file, then read the csv file using powershell and write each row out as an item (see below). You can also, setup the site columns before hand to be of the appropriate type.

Code assumes you have SharePoint PnP installed and that the view is tab delimited

# read the records in that you want to write out to the Destination Site
$rows = Import-CSV -Path $data_to_migrate -Delimiter `t

# connect to the destination site
Connect-PnPOnline -Url $DestinationSite -Interactive

Foreach ($row in $rows) {
  Add-PnPListItem -List $DestinationList -Values $row

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.