SQL Server – Identify Column Causing Truncation Error in Insert Into

Just want to clarify that this is a problem at run time, not compile time, and has nothing to do with the fact that they are in the same transaction. For example, let's assume we have this table:

CREATE TABLE dbo.floob(a int);

The following batch parses successfully (compile time), but at runtime it gets the error you mention in the question:

BEGIN TRANSACTION;
  ALTER TABLE dbo.floob ADD b int;

  SELECT b FROM dbo.floob;
COMMIT TRANSACTION;

In a query editor in Management Studio, you can get around this easily, either by:

1. Highlighting the first two lines, hitting execute, then highlighting the second two lines, and hitting execute; or,

2. Putting a batch separator between them, like so:

   BEGIN TRANSACTION;
     ALTER TABLE dbo.floob ADD c int;
   
   GO
   
     SELECT c FROM dbo.floob;
   COMMIT TRANSACTION;
   

If you are running this from outside of SQL Server (e.g. sending a SQL batch from your application code), you can simply send the two batches separately in a similar manner, or if you absolutely need to send it as a single batch, you can use dynamic SQL (as in Gianluca's answer) to defer name resolution.

You cited two different articles that discuss two different things.

The first article compares insert..select with select into for temporary tables, and the second compares these two in general.

In general insert..select is slower because it's a fully logged operation. select into is minimally logged in the simpleand bulk logged recovery models.

The last comment you cited is about insert into with(tablock), this with(tablock) can make insert into minimally logged under some additional conditions: it should be a heap and have no indexes.

You can find the complete guide here: The Data Loading Performance Guide

It can be summarized in this table:

Note the updates for SQL Server 2016 and later in SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations by Parik Savjani (a Senior Program Manager with the Microsoft SQL Server Tiger team). The updated table is:

Regarding the first article. It discusses the particular case for temporary tables.

The tempdb database is special because it's always in the simple recovery model, and because logging in tempdb is different. On every server restart tempdb is recreated, this means no crash recovery is made for tempdb, and this means that logging in tempdb does not need any "after" image of modified data, only the "before" image to be able to do a rollback if there is a need. This leads to the fact that insert into..select is also minimally logged in tempdb even without tablock hint (in case of a heap that was discussed in the first article).

Conclusion "select into vs insert..select under logging aspect":

In case of a heap, insert into..select performs similarly to select into in case of temporary tables, and in general is slower when tablock hint is not used.

The second aspect is the possibility of parallel execution.

Select into can be executed in parallel starting with SQL Server 2014, and parallel insert...select was first implemented in SQL Server 2016.

I did not reproduce any performance difference between select into and insert into ..select for temporary tables on SQL Server 2012, all executed in serial.