Today’s tasks have included taking a list of identifiers and names and converting them into a series of SQL statements to create, populate and drop temporary database tables. If I just had one or two lines in the form:

56,DataIdentifier

then the easiest way to convert them into:

CREATE TEMPORARY TABLE DataIdentifier (
  event_id int(10) UNSIGNED NOT NULL,
  status varchar(3) NOT NULL,
  PRIMARY KEY (event_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO DataIdentifier
SELECT event_id, IF(has_data,'Yes','No') AS status
FROM BigDataTable
WHERE data_id = 56;

DROP TABLE DataIdentifier;

would be by hand. With getting on for a 100 lines, that was not going to fly. In the past, I have tended to pull up a spreadsheet to accomplish such a task (splitting into columns by the comma and then using CONCATENATE statements to create the required expressions). However, that would be a fairly mammoth concatenation and I had an inkling that there would be a better way of doing it without leaving Vim (in which I was editing the list).

It turns out that the trick was to use backreferences. If you break the initial regular expression into a series of chunks, enclosed in (escaped brackets) , you can then refer to the result of each chunk as 1, 2, etc. The following, colon-delimited substitution command can be run on a range to produce the desired result:

s:(w+)(,)(w+):CREATE TEMPORARY TABLE \3 
(\r  event_id int(10) UNSIGNED NOT NULL,\r  status 
varchar(3) NOT NULL,\r  PRIMARY KEY (event_id)\r) 
ENGINE=MyISAM DEFAULT CHARSET=latin1;\r\rINSERT 
INTO \3\rSELECT event_id, IF(history_of_condition,
'Yes','No') AS status\rFROM BigDataTable\rWHERE 
data_id = \1;\r\rDROP TABLE \3;\r:

It is a monster but it does the job beautifully. Now I just need to get all the DROP TABLE statements out of the way to the end so I can do something with all the temporary tables before zapping them.