2

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.

2
  • Verbose here is just more opportunities for a mistake.
    – paparazzo
    Commented Jun 27, 2018 at 17:16
  • 1
    Not automated and not free, but worth an honorable mention here is RedGate SQL Prompt. The youtube clip for insert into doesn’t quite do it justice, but scrolling through an insert statement, it highlights the column in both the insert and select blocks at the same time (plus a bunch of informative tool tips) - making this exact sniff testing super user friendly. Commented Jun 27, 2018 at 20:47

4 Answers 4

2

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.

1
  • Although not an "answer," the lack of an answer existing is as close as we are going to get. Just wasted another couple hours tracking down a bug due to this issue again, and it's downright infuriating. Commented Aug 1, 2019 at 15:36
1

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.

1
  • Gah I really hate seeing horizontal scroll bars, but yeah that would work! Commented Jun 27, 2018 at 18:44
1

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.

1
  • The problem got me even for a simple statement (I appended a new column in a different spot in the SELECT than the column list). Commented Jun 27, 2018 at 18:51
0

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.

3
  • It seems reasonable that IntelliSense should be capable of this particular validation. But it doesn't even flag warnings when you do silly stuff like DECLARE @i INT = '0' Commented Jun 27, 2018 at 16:13
  • @Elaskanator it can convert that to an INT. It won't work with an 'A'. Commented Jun 27, 2018 at 16:14
  • @sp_BlitzErik I said it was silly, not invalid ;) Commented Jun 27, 2018 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.