I like to be verbose in my coding, so my typical insert statement looks like this:
INSERT INTO MyTable ( Column1 ,Column2 ,Column3 ,Column4 ,Column5 ,Column6 ) SELECT Column1 = 'some value' ,Column2 = 'some value' ,Column4 = 'some value' ,Column3 = 'some value' ,Column5 = 'some value' ,Column6 = 'some value' ....
If you didn't notice the problem with this statement, then you have been fooled just as I have been numerous times.
And the really insidious part of this type of flaw is that it might work for ages before causing any issues, and when it does fail, if there even is an error, it will not indicate the true error.
For example, if columns 3 and 4 are numeric and string types, respectively, then only some values will cause runtime errors for column 4 (and none for column 3) with a type conversion failure (e.g. '0' converts to a number just fine but 'asdf' will not).
(In my case, this was buried inside a Service Broker activation procedure outside the guarded code section (it was a logging statement) so instead of even that error, I was getting the orphaned transaction error masquerading as a poison message disabling my queue.)
Is there any way to validate such a statement that doesn't require manual proofreading?
P.S. I realize that you can give whatever aliases you like, but in a completely optional context such as an
INSERT statement, it would be extremely useful to have some sort of automated tool (i.e. IntelliSense) to point out probable accidental code.
Update: I have opened this feedback item to Microsoft, for this clearly-lacking functionality.