SQL Generation & Templating

Introduction

I recently answered a question (http://e-e.com/questions/29100944) about updating rows in a table. This was a self-join scenario where some of the rows served as update data sources for other rows with matching keys.

Most of the proposed solutions involved using T-SQL to generate the Update statements or a stored proc. I took a different approach which proved quite flexible and produced testable results quickly. I used SQL as replacement pattern for a few Find/Replace operations in a text editor. Since I am a fan of regular expressions, my text editor of choice is Notepad++. Since many solutions involve iteration with your users, flexibility and iteration speed are important characteristics.

User Requirements

Initially, there were only two types of columns to consider, date and non-date. Bit column types were added later. There are nearly 350 columns in this table and only two columns (the Primary key column: ID, and the source/target indicator: SkipImport) are excluded from the updating process.

  1. Update statement for non-date columns, replace if TGT is Null and SRC Not Null 
    Update TGT 
Set TGT.$1 = SRC.$1
From Employeestbl TGT inner join Employeestbl SRC  
 on TGT.[Email] = SRC.[Email] 
Where TGT.SkipImport = 0 And SRC.SkipImport = 1 
And TGT.$1 is null and SRC.$1 is not null;
  2. Update statement for date columns, replace if TGT is < SRC and prior condition 
    Update TGT 
Set TGT.$1 = SRC.$1
From Employeestbl TGT inner join Employeestbl SRC  
 on TGT.[Email] = SRC.[Email] 
Where TGT.SkipImport = 0 And SRC.SkipImport = 1 
And ((TGT.$1 is null And SRC.$1 is not null) 
 OR (TGT.$1 is not null And SRC.$1 is not null And TGT.$1 < SRC.$1)
 );
  3. Update statement for bit columns, update if:
    Sender is 1 and receiver is either 0 or null,
    then field should be updated to 1. 
    Update TGT 
Set TGT.$1 = SRC.$1
From Employeestbl TGT inner join Employeestbl SRC  
 on TGT.[Email] = SRC.[Email] 
Where TGT.SkipImport = 0 And SRC.SkipImport = 1 
And (TGT.$1 is null Or TGT.$1 = 0) and SRC.$1 = 1

Input

The user supplied the DDL for the table:

Process

Here’s where regular expressions can really help you do some templating magic. In Notepad++, you need to set the Search Mode to “Regular Expression” when you display the Find/Replace dialog.

  1. I have to delete the non-field-defining lines.

I initially did this manually, but the Find/Replace commands are:
Find what: CONSTRAINT[^$]+
Replace with:

Find what: CREATE TABLE [^\r]+\r\n
Replace with:

  1. I have to delete the ID and SkipImport lines.

I initially did this manually, but the Find/Replace commands are:
Find what: \t[(ID|SkipImport)][^\r]+\r\n
Replace with:

The remaining DDL lines are transformed with one Find/Replace operation per type of column (date, bit, everything else). As with such processing, exceptions (special cases) are processed first. The Update statements (above) are modified so that they are on a single line.

Find what: \t([.+?]) [(?:smalldatetime|datetime)] [^,]+,
Replace with: Update TGT Set TGT.$1 = SRC.$1 From Employeestbl TGT inner join Employeestbl SRC on TGT.[Email] = SRC.[Email] Where TGT.SkipImport = 0 And SRC.SkipImport = 1 And ((TGT.$1 is null And SRC.$1 is not null) OR (TGT.$1 is not null And SRC.$1 is not null And TGT.$1 < SRC.$1));

Find what: \t([.+?]) [bit] [^,]+,
Replace with: Update TGT Set TGT.$1 = SRC.$1 From Employeestbl TGT inner join Employeestbl SRC on TGT.[Email] = SRC.[Email] Where TGT.SkipImport = 0 And SRC.SkipImport = 1 And (TGT.$1 is null Or TGT.$1 = 0) and SRC.$1 = 1;

Find what: \t([.+?]) [[^,]+,
Replace with: Update TGT Set TGT.$1 = SRC.$1 From Employeestbl TGT inner join Employeestbl SRC on TGT.[Email] = SRC.[Email] Where TGT.SkipImport = 0 And SRC.SkipImport = 1 And TGT.$1 is null and SRC.$1 is not null;

Notes:

  • Removing the leading tab character with the edit gives good visual clue for checking the find/replace results

  • Since the result is > 64K characters, the resulting SQL has to be broken up into two executions.

Transformed DDL

When these three transformational Find/Replace operations have been invoked, this is what we’re left with:

Conclusion

This solution was simple and quick. While I referred to this as a template soluction I think it is almost an inversion of the typical ‘template’ configurations you will encounter. Maybe this would be better described as a macro solution in the traditional sense of expansion/substitution of code.

Although performance wasn’t discussed, the table should have a non-unique index on the Email column to optimize the joining operations.

Written on June 3, 2018
[ tech  regex  ]