sqlsql-servert-sqlselectinsert

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


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.


Solution

  • 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.