Further Updates to the Testable SQL Script Template

I have been using the original and the updated versions of the testable SQL scripts for some time now, and they have proven very helpful on many occasions.

Recently, I have been using the script template at work to create operations that correct data values in test and production environments. Since others have to examine the output of the scripts, I wondered if there was a way to improve the output and to handle some other unsavoury little items that have nagged at me in the past.

So here is yet another update to the template:

DECLARE @mode VARCHAR(1) = 'R' -- set to 'R' for Report or 'U' for update
----------------------
-- Replace the following
-- _dataTable_:       Name of the table being modified.
-- _reportColumns_:   The columns to be shown in the format: _reportColumn_[,...].
--                    May be * to report all columns.
--                    Should include all _affectedColumn_s.
-- _selectionClause_: Clause used to select all rows to be altered.
-- _affectedColumn_:  Specific column to be altered.
-- _newValue_:        Altered value for the column.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION Tmain

IF OBJECT_ID('tempdb.dbo.#tempTable', 'U') IS NOT NULL DROP TABLE #tempTable;

SELECT '                    ' AS _dataTable_
     , _reportColumns_
  INTO #tempTable 
  FROM _dataTable_  
 WHERE 1 = 0
 UNION SELECT NULL
     , _reportColumns_
  FROM _dataTable_ 
 WHERE 1 = 0;

-- If data is to be UPDATED, use: --
INSERT INTO #tempTable
SELECT 'Original Values:' AS _dataTable_
     , _reportColumns_ 
  FROM _dataTable_
 WHERE _selectionClause_;

UPDATE _dataTable_
   SET _affectedColumn_ = _newValue_
     , _affectedColumn_ = _newValue_ ...
OUTPUT 'Updated Values:'
     , INSERTED._reportColumns_ 
  INTO #tempTable 
 WHERE _selectionClause_;
--

-- If data is to be INSERTED, use: --
INSERT INTO #tempTable
SELECT 'Original Values:' AS _dataTable_
     , _reportedColumns_
  FROM _dataTable_
 WHERE _selectionClause_;

INSERT INTO _dataTable_
OUTPUT 'Inserted Values:'
     , INSERTED._reportColumns_ 
VALUES (_newValue_ ...)
  INTO #tempTable;
--

-- If data is to be 'UPSERTED', use: --
BEGIN TRANSACTION Tupsert
INSERT INTO #tempTable
SELECT 'Original Values:' AS _dataTable_
     , _reportedColumns_
  FROM _dataTable_
 WHERE _selectionClause_;

BEGIN TRANSACTION Tupsert
  UPDATE _dataTable_
    WITH (UPDLOCK,SERIALIZABLE)
     SET _affectedColumn_ = _newValue_
       , _affectedColumn_ = _newValue_ ...
  OUTPUT 'Updated Values:'
       , INSERTED._reportedColumns_
    INTO #tempTable 
   WHERE _selectionClause_;

  IF @@ROWCOUNT = 0
  BEGIN
    INSERT INTO _dataTable_
    OUTPUT 'Inserted Values:' AS _dataTable_
          , INSERTED._reportedColumns_
      INTO #tempTable
    VALUES (_newValue_ ...);
  END
COMMIT TRANSACTION Tupsert
--

-- if data is to be DELETED, use: --
DELETE _dataTable_
OUTPUT 'Original Values:' AS [_dataTable_]
     , DELETED._reportColumns_ 
  INTO #tempTable 
 WHERE _selectionClause_;

INSERT INTO #tempTable
SELECT 'Remaining Values:'
     , _reportColumns_ 
  FROM _dataTable_
 WHERE _selectionClause_;
--

-- if data is to be REPLACED, use: --
DELETE _dataTable_
OUTPUT 'Original Values:' AS [_dataTable_]
     , DELETED._reportColumns_ 
  INTO #tempTable 
 WHERE _selectionClause_;

INSERT INTO _dataTable_
OUTPUT 'Inserted Values:'
     , INSERTED._reportColumns_ 
VALUES (_newValue_ ...)
  INTO #tempTable;
--

SELECT * 
  FROM #tempTable;

IF @mode = 'U'
  BEGIN
      COMMIT TRANSACTION Tmain
      SELECT 'Changes have been applied to the ' + DB_NAME() + ' database' [notice]
  END
ELSE
  BEGIN
      ROLLBACK TRANSACTION Tmain
      SELECT 'All changes have been rolled back from ' + DB_NAME() [notice]
  END;

A few things to note:

• The DROP and SELECT INTO are used to create a #tempTable that will have the same structure as _dataTable_.
• The WHERE statement purposefully selects 0 records. This will result in the creation of an empty #tempTable.
• The UNION exists as a means to have the identity column in _sourceTable_ (if there is one) be created as a regular column in #tempTable. We do not want an identity column in #tempTable because it will interfere with INSERT and UPDATE operations.
• The keywords INSERTED. and DELETED. must, of course, be placed before each column name.
• It is often easiest to use * for the the _reportColumns_ value and then specify the specific columns to be reported in the final SELECT … FROM #temptable statement.
• Edit: I have added an UPSERT template. The use of the UPDLOCK and SERIALIZABLE hints came from the recommendations of Aaron Bertrand found here.

Some advantages of this form for the template are:

• The Original Values and Altered Values are reported together, even if multiple tables are being changed. This makes it easier to compare changed values.
• The SELECT FROM #tempTable can be customized using ORDER BY in order to improve the visibility of the changes.
• Previously, it was necessary to store values in another temporary table if an UPDATE operation was going to change the values that are used in the _selectionClause_. This is no longer needed, as the data from before and during the change are selected with the same _selectionClause_, and we no longer attempt to select from the _dataTable_ after the change has occurred.