No transactions in the mix anywhere, but more interesting data:

To track down the problem I was having with the final table, which I mentioned in the original post, I moved a portion of the procedure into a plain old query so that I could run that part again and again while tinkering with the actual columns in that CREATE TABLE line (and a select into insert line that followed it). By doing that, I came across some interesting data:

Taking just this SQL:

IF OBJECT_ID('TestTable', 'U') IS NOT NULL
DROP TABLE TestTable

CREATE TABLE TestTable

The If was essential, because I neglected to add a Drop Table at the end of the query, so TestTable remained. If I didn't have the If statement, then the Create Table line would fail because the table already existed. That is exactly what anybody would expect. What I was doing was that I had about fifty columns in the original TestTable, and the subsequent INSERT would fail because I had one of the columns wrong (actually, about four of the columns were wrong, but that's a different matter). Since I wasn't sure which of the columns were wrong for reasons I won't go into, I was taking off about 5-10 columns, then trying it again, to track down the problem fields. Therefore, in each test, the set of columns was getting SMALLER!

Eventually, I tracked down part of the problem and solved it, then replaced the truncated list of columns with the full list to test it again (which means that the list of columns got BIGGER), and I immediately got an error about lots of invalid columns in the Insert. Now, that makes no sense, because the If statement had run and dropped the table, then a new table had been created. This had worked over and over again as I reduced the number of columns. If failed when I increased the number of columns, and the INSERT was saying that the columns didn't exist, but only when I ran the query. At design time, if you had a column in the insert that didn't match a column in the target table, there would be a visible error, but that didn't happen because the columns were the same between the create and the insert statements.

So, as long as the column list stayed the same or shrank, the If statement correctly dropped the existing table, then the create statement correctly built the new table. If the If statement wasn't there, then the Create wouldn't work because the table already existed, which shows that the Drop was essential. However, if the number of columns increased, then it was as if both the If and the Create were skipped entirely.

Technically, it doesn't matter whether the columns shrank or not. Reducing the number of columns in the INSERT would still work even if the Drop and Create had not happened, as it would still be valid SQL. However, once you add columns to the INSERT, then the Drop and Create MUST run, or else things will fail. So, it appears that what is happening may be some kind of optimization. You can't run the Create with the table already in existence, but the SQL compiler may be skipping both the Drop and the Create if the table is already there. That isn't right, but the behavior that I have seen would be explained by such an occurence, and by little else. Saying that the Drop and Create are only skipped in cases where columns are added seems really hard to justify, but saying that the Drop and Create are skipped in cases where the table already exists doesn't seem to far fetched, to me (though it would still be a bug).