How to CRUD items in SQL from SharePoint List

There are no any option available directly to create and update items automatically in SQL Database from SharePoint.
We will solve the issue in 3 steps:

1. Creating List in SharePoint
2. Creating Table and Stored Procedure in SQL
3. Using Power Automate as a bridge to sync data.

a.     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:

a.     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 p
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

a.     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.

1. 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 & update store 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.

2. 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.

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. 🙏🙂