sql - Why are dot-separated prefixes ignored in the column list for INSERT statements?

Question

I've just come across some SQL syntax that I thought was invalid, but actually works fine (in SQL Server at least).

Given this table:

create table SomeTable (FirstColumn int, SecondColumn int)

The following insert statement executes with no error:

insert SomeTable(Any.Text.Here.FirstColumn, It.Does.Not.Matter.What.SecondColumn)
values (1,2);

The insert statement completes without error, and checking select * from SomeTable shows that it did indeed execute properly. See fiddle: http://sqlfiddle.com/#!6/18de0/2

SQL Server seems to just ignore anything except the last part of the column name given in the insert list.

Actual question:

Can this be relied upon as documented behaviour?

Any explanation about why this is so would also be appreciated.

Answer 1

It's unlikely to be part of the SQL standard, given its dubious utility (though I haven't checked specifically ^(a)).

What's most likely happening is that it's throwing away the non-final part of the column specification because it's superfluous. You have explicitly stated what table you're inserting into, with the insert into SomeTable part of the command, and that's the table that will be used.

What you appear to have done here is to find a way to execute SQL commands that are less readable but have no real advantage. In that vein, it appears similar to the C code:

int nine = 9;
int eight = 8;
xyzzy = xyzzy + nine - eight;

which could perhaps be better written as xyzzy++; :-)

I wouldn't rely on it at all, possibly because it's not standard but mostly because it makes maintenance harder rather than easier, and because I know DBAs all over the world would track me down and beat me to death with IBM DB2 manuals, their choice of weapon due to the voluminous size and skull-crushing abilities :-)

---

^(a) I have checked non-specifically, at least for ISO 9075-2:2003 which dictates the SQL03 language.

Section 14.8 of that standard covers the insert statement and it appears that the following clause may be relevant:

Each column-name in the insert-column-list shall identify an updatable column of T.

Without spending a huge amount of time (that document is 1,332 pages long and would take several days to digest properly), I suspect you could argue that the column could be identified just using the final part of the column name (by removing all the owner/user/schema specifications from it).

Especially since it appears only one target table is possible (updatable views crossing table boundaries notwithstanding):

<insertion target> ::= <table name>

Fair warning: I haven't checked later iterations of the standard so things may have changed. But I'd consider that unlikely since there seems to be no real use case for having this feature.