There is not any option available directly to create and update items automatically in SQL Database from SharePoint. This was a very important part of one of our project and as a solutions consultant we must solve this to provide service to our clients.
We will solve the issue in 3 steps:
- Creating List in SharePoint
- Creating Table and Stored Procedure in SQL
- Using Power Automate as a bridge to sync data.
1. Creating List in SharePoint
We will use a demo for this issue to understand it better.
Suppose I have a SharePoint List Name “Product List” and the Columns are:
2. Creating Table and Stored Procedure in SQL
In this step we will create a table in our SQL Database and a Store Procedure that will be used to create and update items in SQL. Create a table by executing the following query –
CREATE TABLE ProductList ( Id int, ProductName varchar(255), ProductType varchar(55), Dispatched_To varchar(255), Date_of_Dispatch varchar(55) );
Form the above, the Id field is created additional to store the Id of each item of the list. Create a SQL store procedure by executing the following query –
/*For Create and Update*/ CREATE PROCEDURE ProductList_CreateUpdateSP ( @ID int, @ProductName varchar(255) = "", @ProductType varchar(255) = "", @Dispatched_To varchar(255) = "", @Date_of_Dispatch varchar(255) = "", @StatementType varchar(55) = '' ) AS BEGIN IF @StatementType = 'INSERT' BEGIN INSERT INTO dbo.ProductList (ID, ProductName, ProductType, Dispatched_To, Date_of_Dispatch) VALUES (@ID, @ProductName, @ProductType, @Dispatched_To, @Date_of_Dispatch) END IF @StatementType = 'UPDATE' BEGIN UPDATE dbo.ProductList SET ProductName = @ProductName, ProductType = @ProductType, Dispatched_To = @Dispatched_To, Date_of_Dispatch = @Date_of_Dispatch WHERE ID = @ID END END
The above procedure will take all fields as parameter and an addition parameter I added i.e., Statement Type. This will decide whether a user is updating or creating an item and will be used in Power Automate. Also, I initialized the parameters value with empty string so if any optional field is not field by user then it will be empty in SQL otherwise it will throw an error.
Now, for Delete, create another store procedure by executing the following query –
/*For Delete*/ CREATE PROCEDURE ProductList_Delete ( @ID int ) AS BEGIN DELETE FROM dbo.ProductList WHERE ID = @ID END
3. Using Power Automate as a bridge to sync data
Go to https://flow.microsoft.com and click on Create then Automated Cloud Flows then Skip.
a. For creating and Updating – Select the trigger “When an item is created or modified”
After that enter the SharePoint site address where you created the list and then select your list in the next column.
Click on +New Step and add a condition
In the first field select SharePoint Dynamic Content “Created” Column and in the second field select the “Modified” dynamic content. This condition will check if your created and modified date is same or not.
For a common sense if created date/time and modified time is equal that means a new item is created and if not, then an existing item is modified because when you edit an exiting item in SharePoint List, its just changes the modified column as created column’s value assigned once.
Now in the Yes field of the condition Add an action
Connect with your SQL Database and then select the create and update procedure that you created. The parameters will start showing automatically, put SharePoint dynamic data accordingly-
*Note: All parameter’s value will be selected dynamically (except Statement Type, this will be written to decide whether update or create)
In the No Field of the condition all steps are as same as Yes (except Statement Type, in this case it will be “UPDATE” to update an existing item)
Now the flow is ready, save the flow and whenever you create or update an item it will subsequently create or update in SQL.
b. For Deleting items: Create a new flow but this time the trigger will be “When an item is deleted”
Configure by selecting site and list and click +New Step and select “Execute Stored Procedure (V2)”
and select the stored procedure of delete that you created. It will require on parameter i.e., ID. Insert Dynamic content ID and save the flow.
Now, every item you create, modify or delete any item in SharePoint will also act same in Microsoft SQL Server Database.
Well, That’s the end of this post. Let me know if you have any query or suggestion in comment below. I’ll see you next time. Reach me out on social handlers below
Author: Dipak Shaw
- Top 10 Reasons Your Workplace Should Get on the Cloud With Microsoft 365
- How the New Power BI is More Beneficial for Business
- Develop An App Like Uber in 2021 – Innoventix Solutions
- Why You Should Choose Flutter For Building Your Career In 2021: 5 Reasons You May Not Know
- 7 Benefits of a SharePoint based Document Management System