Click on the Query Design button under the CREATE tab and select the SQL database table. Click on the Append button under the ribbon DESIGN tab to append data to the SharePoint list. Select how the data from the SQL database table should be added to the SharePoint list.
Please follow the below steps.
- Connect to the SharePoint Online site and get the reference of the SharePoint list.
- Connect to the SQL database using a connection string and execute the SQL query.
- Iterate through all the rows in the data table and create items in SharePoint List.
With Microsoft SharePoint Designer 2010, you can connect to a variety of databases, including Microsoft SQL Server, Oracle, and other databases that are accessible by using the OLE DB protocol. By default, your Microsoft SharePoint site does not contain any database connections.
Re: Sync SharePoint Online List to on-premise SQL Server
- Get all existing items in the list.
- Delete all the items from the list.
- Get all the rows I want from the on-premise SQL Server. There was already an on-premise data gateway in place.
- Create a new item in the list for each row retrieved.
The simplest way to get started with Business Data Connectivity Model is to:
- Run Microsoft SharePoint Designer.
- Connect to the destination site.
- Select External Content Types from the Navigation pane.
- Select External Content Type button from the External Content Types ribbon menu.
Connecting SharePoint online to Azure SQL database via BCS
- Login in Azure and click on SQL databases in Left hand panel. Click on New . Select Data Services->SQL Datatable->Quick Create.
- Provide Database name. Under Server, New SQL database server. Select Region (nearest for you) . Enter Login name and password.
On the SharePoint Central Administration website, click Application Management. In the Databases section, click Manage content databases. On the Manage Content Databases page, click Add a content database.
On the Tools menu, click Data Connections. In the Data Connections dialog box, click Add. In the Data Connection Wizard, click Create a new connection to, click Submit data, and then click Next. Under How do you want to submit your data, click To a document library on a SharePoint site, and then click Next.
Create an external content type in SharePoint to connect to SQL…
- Open SharePoint Designer and open your site.
- Select “External Content Types” from the “Navigation menu.
- Select “External Content Type” from the “New” ribbon section.
- Rename the content type by selecting the name and entering a new one.
- Create an Excel spreadsheet that has the SQL query to the database.
- Execute the query and do a Copy Values copy in Excel and paste that to a new workbook.
- Import that workbook when creating the Sharepoint list.
Sync with a new SharePoint site
- In Project, choose File > Save.
- Select Sync with SharePoint, and in the Sync with list, select New SharePoint Site.
- In the Project name box, type a name.
- In the Site Address list, add the address of the SharePoint tasks list that you will sync to. …
- Choose Save.
An external list is a key building block for SharePoint solutions based on external data. You can read and write external data with the familiar experience of using a SharePoint list. An external list looks and behaves a lot like a native list, but there are some differences in behavior.
Add a single item to a list
- Navigate to the site containing the list where you want to add an item.
- Above the list, select the + New or + New item link to open the New item window.
- Enter the information in the list item.
Click on the Append button under the DESIGN tab in the ribbon and select the SharePoint list where you want to have the example data from the dropdown in the Append dialog. Click OK. Append the query columns to suitable fields in the SharePoint list.