SQL Server – How to Validate Insert Statements for Mismatching Column Names

error handlingsql server

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.

Best Answer

I don't think there's any good way to automate this. Especially since most people, in my experience, who use INSERT ... SELECT have not written the SELECT specifically for the INSERT, but rather copied it from somewhere else. If they have written the SELECT for the INSERT, it's unlikely they went to the trouble of adding column aliases (whether the source is another table with different column names, or just a series of constants), and if they did add column aliases, it's not guaranteed that they named them in such a way that there is a 1:1 mapping of column names. If we really are talking about INSERT ... SELECT (a bunch of constants), it's much more likely to be INSERT ... VALUES().

You could submit a feature request to Microsoft, but I think you'd have to lay out the use case very thoroughly for this to gain traction over the many other improvements the community wants to see.

Related Question