SQL Server - Any way to validate insert statements for mismatching column names?

Question

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.

Answer 1

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.

Answer 2

If I'm having a particularly bad hair day, I'll write my code 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'

and it's easier to see that I've made a mistake.

Answer 3

I've ran into this problem enough times that my INSERT ... SELECT statements look like this:

with data as (
  <complex SQL>
)
INSERT INTO MyTable (
  <list of columns>
)
select
  -- copy+paste from INSERT clause
  <list of columns>
from data;

I prefer CTEs over sub-queries.

By using copy+paste, this method helps ensure that you have all of the columns listed in the same order.

Answer 4

Well since the data in source and destination tables are not linked in any permanent way that I know of and the only way of validating is to check for data type correctness, then I believe SQL does not really have any other way of telling if your query is really REALLY REAAAAALLLY what you want to achieve. Maybe some AI in the future :)

With temporary tables you might try SELECT INTO instead of INSERT..SELECT - this way the column order might be different after creating, but you will get the correct column when selecting from such a temp table.