Automatically match columns in INSERT INTO ... SELECT ... FROM ...

Question

Hi!

SQL Server question. When doing

INSERT INTO T1 SELECT (C1, C2) FROM T2

I don't want to specify column names of T1 because they are the same as in T2

Is it possible to do so?

Currently I'm getting error

Msg 213, Level 16, State 1, Line 1

Column name or number of supplied values does not match table definition.

Answer 1

Why not simply

INSERT INTO t1
SELECT * FROM T2

Answer 2

Yes, you can omit the field names for the table that you insert to, and you can use select * to get all fields from the table, but I would not recommend this approach.

If you omit the field name the fields are matched by position, not by name. If the fields are not in the exact same order, they will be mixed up. Generally you should avoid relying on the exact layout of the tables, to minimise the risk that changes in the tables breaks the queries.

Answer 3

If T1 and T2 match exactly you have two choices. You can either select all columns from T2 for the insert into T1, or you can provide a column list to the insert statement.

Even though when you do a select MSSQL provides column headers that information is not used by an insert statement to match columns up.

Answer 4

Always use explicit columns both in the INSERT and in the SELECT projection. Even if you don't want to, you should:

INSERT INTO T1 (C1, c2)
SELECT C1, C2 FROM T2

Answer 5

If you're worried about column names you can always alias them:

INSERT INTO T1 (C1, c2)
SELECT C1 AS C1_ALIAS, C2 AS C2_ALIAS FROM T2

Or, more succinctly:

INSERT INTO T1 (C1, c2)
SELECT C1 C1_ALIAS, C2 C2_ALIAS FROM T2

Though I can't really think why one would want to in such a simple example