Steve Chambers Steve Chambers - 1 month ago 5
SQL Question

Is there a SQL Formatter that can append column names comments to INSERTed values?

I'm looking for an SQL formatter that will do this:

INSERT INTO MyTable (col1, col2, col3, col4)
VALUES (
1, -- col1
2, -- col2
3, -- col3
4 -- col4
)


I can't seem to find this feature in any of the free online SQL formatters, although it could be that I haven't looked hard enough - hence the question. Surely such a thing exists - or if not it looks simple enough that I'm tempted to try and write one myself...

For anyone unconvinced about the value of doing this here is one of the (many) actual inserts - very hard to tell what the values represent without the inline commenting (and having one value per line is useful too):

INSERT INTO ForecastAcqControl
( ForecastImageServiceId, LayerId, Style, IsForecast, IsTiled,
LatSW, LongSW, LatNE, LongNE, PixelsWidth, PixelsHeight, ZoomLevels,
TimeCoverageHours, TimeStepMinutes, UpdateIntervalMinutes, CreatedDT
)
VALUES
(
1, -- ForecastImageServiceId: OBSERVATIONS
1, -- LayerId: RADAR_UK_Composite_Highres
NULL, -- Style
FALSE, -- IsForecast
FALSE, -- IsTiled
47, -- LatSW
-15, -- LongSW
61, -- LatNE
5, -- LongNE
1000, -- PixelsWidth
1200, -- PixelsHeight
4, -- ZoomLevels
-2, -- TimeCoverageHours
5, -- TimeStepMinutes
5, -- UpdateIntervalMinutes
UTC_TIMESTAMP() -- CreatedDT
);

Answer

Finally got round to creating a utility for doing this myself. (In retrospect, regular expressions may not have been the best way to go - but got there in the end!)

The tool is here: SQL Insert Commenter

Have done a fair amount of testing but there's bound to be something it doesn't work for so would be grateful if anyone trying it could let me know of any issues...

Comments