Updating SQL Tables from Excel: A New Approach (Part 2)

In part 1 of this article, we have prepared the table for the update. In this second part, we will learn how to update the SQL table with only the rows modified by the user.

Part 2: Tracking Changes and Updating the SQL Table

Tracking the changed rows

We need a way to track what has been changed by the user in order to update the SQL table with only the modified rows and thus avoid any unnecessary workload on the server.

This is where the “XL3TrackChanges” function comes into place. We will use it to flag changed rows, which will then make it possible to isolate them thanks to the “XL3Filter” function.

Here the syntax:

XL3TrackChanges( Clear, Input, Output )

Where the parameters are :

ParameterDescription
ClearA cell reference that should contain TRUE when the list of tracked changed rows should be cleared.
InputThe range to watch for changes.
OutputThe range to mark when change occur.

So in our example, the formula is:

XL3TrackChanges(T5, salary:commission, changed )

Where:

ParameterDescription
T5When T5 is set to TRUE, all the tracked changes (value equals TRUE in the “Output” range – i.e. column P) will be cleared.
salary:commission i.e. the named ranges we defined for columns N and O.
changedThe range of cells XL3TrackChanges will update by setting the value TRUE for each modified row (i.e. column P).

Here is what it looks like:

XL3TrackChanges Formula

So, now, whenever a value is changed in the “salary” or “commission” range (i.e. columns N and O), XL3TrackChanges writes TRUE in the “changed” column (i.e. column P):

XL3TrackChanges Formula Output

Thanks to the “XL3Link” formula we defined earlier for the “Clear Edits” button that sets $T$5 to TRUE, the marked changes in the “changed” named range (column P) will be cleared when clicked.

To summarize how the whole thing works:

  1. When clicking the “Clear Edit” link in $N4$, the “XL3Link” function set $T$5 to TRUE
  2. This fires the “XL3RefreshObjects” formula in $U$5 (please refer to part 1 of this article): the table gets refreshed, reverting any changed values to the originals
  3. It also fires the “XL3TrackChanges” formula in $S$5: the “changed” range (i.e. column P) gets cleared
Clear edits link

Updating the SQL table

Rather than executing multiple individual SQL “Update” commands, we want to make use of an SQL stored procedure in conjunction with a User Defined Table Type to bulk update the SQL table.

A Stored Procedure using a User Defined Table Type

In this example, we defined the following in the SQL database:

  • A table type made of 13 columns (as we display 13 columns in our query):

CREATE TYPE [dbo].[XL3ParameterTable13] AS TABLE(
      [ParameterValue1] [nvarchar](100) NULL,
      [ParameterValue2] [nvarchar](100) NULL,
      [ParameterValue3] [nvarchar](100) NULL,
      [ParameterValue4] [nvarchar](100) NULL,
      [ParameterValue5] [nvarchar](100) NULL,
      [ParameterValue6] [nvarchar](100) NULL,
      [ParameterValue7] [nvarchar](100) NULL,
      [ParameterValue8] [nvarchar](100) NULL,
      [ParameterValue9] [nvarchar](100) NULL,
      [ParameterValue10] [nvarchar](100) NULL,
      [ParameterValue11] [nvarchar](100) NULL,
      [ParameterValue12] [nvarchar](100) NULL,
      [ParameterValue13] [nvarchar](100) NULL
)
GO

  • A stored procedure which takes a “XL3ParameterTable13” table type as only argument to manage the update:

CREATE PROCEDURE [dbo].[ap_UpdateEmployee2] @EmployeeValues XL3ParameterTable13 READONLY
AS
BEGIN
      SET NOCOUNT ON;
      Update Employees
      Set salary = ISNULL(CAST(v.ParameterValue11 as decimal(10,2)), Employees.salary),
            commission_pct = ISNULL(CAST(v.ParameterValue12 as decimal(10,2)), Employees.commission_pct)
            from @EmployeeValues v
      Where Employees.employee_id = CAST(v.ParameterValue1 AS int)
END

Executing the stored procedure

To execute our stored procedure we make use of the “XL3RunSqlProc2” function.

Here the syntax:

XL3RunSqlProc2(ExcecuteSQL, Connection, ProcName, [Parameter1Name], [Parameter1Value],..., [ParameterNName], [ParameterNValue])

Where the parameters are :

ParameterDescription
ExcecuteSQLThe cell that will fire the SQL3RunSqlProc2 when set to TRUE.  
ConnectionThe workbook connection ID use to connect to the SQL database.  
ProcNameThe name of the stored procedure to run  
[Parameter1Name], [Parameter1Value]The name and value for the parameter to be passed to the stored procedure. You can pass up to 10 pairs.

Passing only the changed rows as argument to the stored procedure

We now have the required syntax to execute our stored procedure but how do we pass the “Parameter1Value” for the “EmployeeValues” argument to our “XL3RunSqlProc2” function? In other words, how do we pass a table containing only the changed rows?

This is when the “XL3Filter” function come in to play. Here its syntax:

XL3Filter( Input, Filter )

Where the parameters are :

ParameterDescription
InputThe range to watch for changes.  
FilterThe range (column) that contains TRUE values to corresponding changed rows.  

In our case, making use of the named ranges we defined:

XL3Filter(data,changed)

XL3Filter formula

The above capture is only here to help understand what the XL3Filter does. In our example, we actually don’t need to display it but rather pass it as the value argument to our stored procedure.

Bringing all the pieces together

We can now merge all the pieces together. The final syntax to execute our stored procedure becomes:

XL3RunSqlProc2(P5,1,"ap_UpdateEmployee2","EmployeeValues",XL3Filter(data,changed))

Where:

ParameterDescription
P5When P5 = TRUE, the stored procedure will fire.  
1The XLCubed connection ID for the SQL database used in our workbook  
ap_UpdateEmployee2The name of our stored procedure  
EmployeeValuesThe name of the argument for our stored procedure
XL3Filter(data,changed)The value for the argument which is a table containing only the changes rows.
XL3RunSQLProc2 formula

Of course, we need an additional “XL3Link” formula to set $P$5 to TRUE and thus trigger the “XL3RunSqlProc2” function that will in turn execute our stored procedure. This is what the formula associated to the “Save” link in $O$4 does:

XL3Link(,"Save",,XL3Address(P5),TRUE,XL3Address($T$5),TRUE)

XL3Link formula to run update sql proc

So, let’s click that “Save” button and update our SQL table!

SQL report with edits
SQL report with edits saved

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.