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:
SELECT INTOare used to create a
#tempTablethat will have the same structure as
WHEREstatement purposefully selects 0 records. This will result in the creation of an empty
UNIONexists 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
#tempTablebecause it will interfere with
Some advantages of this form for the template are:
Altered Valuesare reported together, even if multiple tables are being changed. This makes it easier to compare changed values. The
SELECT FROM #tempTablecan be customized with
ORDER BYin order to improve the visibility of the changes.
- Previously, it was necessary to store values in temporary table if an
UPDATEoperation 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.