Does INSERT INTO ... SELECT ... always match fields by ordinal position?

Question

My tests seem to confirm that

INSERT INTO a (x, y) SELECT y, x FROM b

maps b.y to a.x, i.e., the fields are matched only by ordinal position and not by name. Is this always the case, i.e., can I rely on that behaviour? Unfortunately, the documentation does not specify this (or I didn't find it).

Facula

Consider if b did not have either x, or y. Ordinal is the only way that makes sense. INSERT INTO a (x, y) SELECT f, g FROM b – Coagulate

Answer 1

That is correct, SQL Server does not try to do any mapping of column names since you can apply any aliases to the source data that you like. It will always reference ordinal position.

Amaleta

Answer 2

Yes, you are correct.

The order of the fields in the INSERT INTO statement does not need to match the table definition.

But the alias/field names of the SELECT will be ignored, and the values inserted into the fields named by the INSERT INTO statement.

CREATE TABLE test (
  a      AS INT,
  b      AS INT,
  c      AS INT
)
INSERT INTO
  test (
    b,
    c,
    a
  )
SELECT
  1 AS a,
  2 AS b,
  3 AS c

SELECT * FROM test

 a | b | c
---+---+---
 3 | 1 | 2

Neau

Answer 3

Yes. It's because is not using ordinal position, it's just resolving the query by parts. First it Select from b, not taking into accounts columns names, since SQL don't resolve things using column names (you can make joins of different columns names). Then it will add the data to a, in the same way you specify the select in b. In fact, if you change the order, it will be inserted in other order ;)..

Cellist