Knowledge Base

How to sync data between SQL Server and SharePoint list based on a SQL statement?

With Data Connector, you can sync data from SQL server, MySQL or Oracle database to a SharePoint list. You can also sync based on a query statement to get the desired targeted data.

In the article, we will demonstrate how to sync data based on a SQL statement from SQL server to a SharePoint list.

WHAT YOU'LL GET

Enter a SQL clause to choose data from SQL Server to sync to SharePoint list

WHAT YOU'LL NEED

Data Connector

The sample data we use for this case

Create a list named as Order List from the custom list template on a SharePoint site, the list contains several columns which are the same as that in the Purchase Orders table.

Sync data between SQL Server and SharePoint list based on a SQL statement-Part 1

  1. Download the trial version of Data Connector and install it to your SharePoint server.

  2. Navigate to the site in which the Purchase-Orders is located.

  3. In the upper right corner, click Settings and then click Site Settings.

  4. On the Site Settings page, click Data Connector Settings under BoostSolutions Software section in the bottom right corner of the page.

  5. On the Data Connector Settings page, click Create Job.

  6. After clicking Create Job, you will enter the Edit Job page.

    In the Job Name section, specify a unique name for the job, such as job-1.

  7. In the Direction section, specify the direction for the data connection. Select From external data to list option which means we will sync data from the external data source to this SharePoint list.

  8. In the List section, specify a SharePoint list or Document Library to which you want to sync data from the external data source. Select Choose an existing list option. Click to browse and select the Purchase-Orders from the drop-down list.

  9. In the External Data and Authentication section, configure the settings as following. Specify the external data type and database authentication. (Ensure the user account you specify has the appropriate permissions to access the external data source.)

    External Data Type: Select SQL Server as the external data type.

    Database Server: Enter the SQL server’s name or its IP address.

    Database Authentication: There are two types of authentication, Windows Authentication and SQL Server Authentication.

    Windows Authentication: Select this authentication if you want to use the application pool account to access the SQL database. You need to enable Windows Authentication as the Authentication Mode in SQL Server in order to access the database successfully.

    SQL Server Authentication: If you select this authentication, you need to enter a SQL user name and password to access the database.

    Here we select the SQL Server Authentication.

    Username: Enter a SQL user name. The user should have Write permission to the database.

    Password: Enter the user’s password.

    Click Connect to connect the SQL Server we specified (the dbserver3 in our case). If everything goes right, it will show Connection successful below the Connect button. And the databases existed on the specified SQL server will be populated to the dropdown list as shown in the picture above.

    Database: Select a database, here we select BoostSolutionsEnterprise.

  10. In the Data Settings section, specify which external data will be connected to the SharePoint list. Select Specify a table/view to filter option, and select dbo.Purchase_Orders (the table Purchase_Orders stored in the BoostSolutionsEnterprise database as we described in the sample data section) from the drop-down list.

    In the textbox below, type the following SQL statement, and click Validate button to verify it.

    where cast([Payment] as nvarchar) = 'Credit' and [purchased date]>= '2017-07-01'

    The SQL statement means that items which Payment is Credit and Purchased date is equal to or greater than 2017-07-01 will be synced to the Purchase-Orders list.

    Here we take SQL server database as example, you can also type query statement for MySQL database or Oracle data sources in the same way.

  11. In the Column Mapping section, map the fields in the external database to the columns in the SharePoint list as following.

    By default, the database fields which have the same name and type with that of the columns in the SharePoint list will be automatically mapped.

    And then select Order No. as the key. A “key column” is one that uniquely identifies records and can be used to compare data sources.

    If the columns name or type are different between SharePoint list and external data source, you have to map the columns manually by clicking Add a column mapping link.

  12. Keep other settings as default and click Save to save the settings.

  13. And then you will find that the job has been created on the Data Connector Settings page, the job-1.

  14. Click the Run button for the job-1 and waiting for the job to be run completely.

  15. After the job run completely, click Close.

    And navigate to the Purchase-Orders list, and you will find that the items in the table “Purchase_Orders” which meet the query condition have been synced to the SharePoint list successfully as following.

Sync data between SQL Server and SharePoint list based on a SQL statement-Part 2

  1. Download the trial version of Data Connector and install it to your SharePoint server.

  2. Navigate to the site in which the Order List is located.

  3. In the upper right corner, click Settings and then click Site Settings.

  4. On the Site Settings page, click Data Connector Settings under BoostSolutions Software section in the bottom right corner of the page.

  5. On the Data Connector Settings page, click Create Job.

  6. After clicking Create Job, you will enter the Edit Job page.

    In the Job Name section, specify a unique name for the job, such as job-2.

  7. In the Direction section, select From external data to list option.

  8. In the List section, select Choose an existing list option. Click to browse and select the Order List from the drop-down list.

  9. In the External Data and Authentication section, configure the settings as following.

  10. In the Data Settings section, select Enter a SQL clause to choose data option.

    In the textbox below, type the following SQL statement, and click Validate button to verify it.

    SELECT TOP (1000) [Order No.]
    ,[Customer]
    ,[Country]
    ,[Product]
    ,[Purchased Date]
    ,[Ext Price]
    ,[Payment]
    FROM [BoostSolutionsEnterprise].[dbo].[Purchase Orders]
    where cast([Payment] as nvarchar) = 'Cheque' and [purchased date]>= '2017-06-01'

    The SQL statement means that items which Payment is Cheque and Purchased date is equal to or greater than 2017-06-01 will be synced to the Order List.

    Here we take SQL server database as example, you can also type query statement for MySQL database or Oracle data sources in the same way.

  11. In the Column Mapping section, map the database fields to the list columns as following.

    And then select Order No. as the key.

  12. Keep other settings as default and click Save to save the settings.

  13. And then you will find that the job has been created on the Data Connector Settings page, the job-2.

  14. Click the Run button for the job-2 and waiting for the job to be run completely.

  15. After the job run completely, click Close.

    And navigate to the Order List, and you will find that the items in the table “Purchase_Orders” which meet the query condition have been synced to the SharePoint list successfully as following.

Related articles:

How to sync data from SQL Server database to a SharePoint list?

How to sync data from a database view to a SharePoint list?

How to sync data automatically from external data sources to SharePoint list based on schedule?

Login