Menu
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.
701
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
You can still insert records into the destination table with specifying column names in the INSERT INTO SELECT statement. We should have an appropriate data type to insert data. You cannot insert a varchar column data into an INT column.
INTO' creates the destination table, it exclusively owns that table and is quicker compared to the 'INSERT … SELECT'. Because the 'INSERT … SELECT' inserts data into an existing table, it is slower and requires more resources due to the higher number of logical reads and greater transaction log usage.
INSERT INTO SELECT statement in SQL Server is used to copy data from the source table and insert it into the destination table. The SELECT INTO statement in SQL Server is used to copy data from one (source) table to a new table. INSERT INTO SELECT requires the destination table to be pre-defined.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
