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
----------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION

IF OBJECT_ID('tempdb.dbo.#tempTable', 'U') IS NOT NULL DROP TABLE #tempTable;
   SELECT '                    ' [_dataTable_], * INTO #tempTable FROM _dataTable_ WHERE 1 = 0
    UNION SELECT NULL, * FROM _dataTable_ WHERE 1 = 0;

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

INSERT INTO _dataTable_
     OUTPUT 'Inserted Values', * INTO #tempTable 
     VALUES ( _affectedColumn_ );
--

-- If data is to be UPDATED, use: --
SELECT 'Original Values:'
     , _affectedColumns_
  FROM _dataTable_
 WHERE _selectionClause_;

UPDATE _dataTable_
   SET _affectedColumns_
OUTPUT 'Updated Values', INSERTED.* INTO #tempTable 
 WHERE _selectionClause_;
--

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

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

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

INSERT INTO _dataTable_
     OUTPUT 'Inserted Values', * INTO #tempTable 
     VALUES ( _affectedColumn_ );
--

SELECT * 
  FROM #tempTable;

IF @mode = 'U'
  BEGIN
      COMMIT TRANSACTION
      SELECT 'Changes have been applied to the ' + DB_NAME() + ' database' [notice]
  END
ELSE
  BEGIN
      ROLLBACK TRANSACTION
      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.

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 with ORDER BY in order to improve the visibility of the changes.
  • Previously, it was necessary to store values in 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s