Menu
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).
288
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
If you are specifying the column names, the order doesn't matter. For example: INSERT INTO TABLE_NAME VALUES ('','','') // Here the values needs to be in order of columns present in your table. INSERT INTO TABLE_NAME (ID, NAME, EMAIL) VALUES ('','','')` // Here, ordering can be changed as per requirement.
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.
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.
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.
160
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
