INSERT INTO selected columns creates new column from JOIN I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the query also creates additional columns in the existing tables to (what appears to be) accommodation for the JOIN... ON statements. Simplified example follows:

TableA

UID   Text_GUID
1     abc123
2     abc345
3     abc456
4     abc678


TableB

Text_GUID   Col_A   Col_B   Col_C   Col_D
abc123       100     200     300     400
abc345       111     211     311     411
abc456       122     222     322     422
abc678       133     233     333     433


Execute the following query:

INSERT INTO [TargetTable] (UID, Col_A, Col_B, Col_C, Col_D)
SELECT A.UID, B.Col_A, B.Col_B, B.Col_C, B.Col_D
FROM [TableA] AS A
INNER JOIN [TableB] AS B
ON A.Text_GUID = B.Text_GUID;


I expect this:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D  
1       100     200     300     400
2       111     211     311     411
3       122     222     322     422
4       133     233     333     433


I get this:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D   Text_GUID
1       100     200     300     400     NULL
2       111     211     311     411     NULL
3       122     222     322     422     NULL
4       133     233     333     433     NULL


A new column is being created with the column name of the JOIN. Each row value for the new column is inserted with a NULL. I don't get it. First of all I don't want the new column created, second I have no idea why the new column is populated with NULL (not that it matters if it isn't created, but I don;t understand why it isn't populated with the value of the JOIN... ON statement if it is created.

My understanding was that by specifying the target column names with the appropriate SELECT I should only INSERT into the appropriate columns.

When I do this with a statement that has more than one criteria for the JOIN, two columns (or more) get created. For example, if the JOIN... ON statement was something like the following:

ON A.Text_GUID = B.Text_GUID AND A.Another_Col = B.Another_Col


I would end up with:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D   Text_GUID   Another_Col
1       100     200     300     400     NULL        NULL
2       111     211     311     411     NULL        NULL
3       122     222     322     422     NULL        NULL
4       133     233     333     433     NULL        NULL


This particular query runs against anything from 150 million rows to 8 billion rows, so if I can avoid it I do not want to put everything into a temporary table first as this is part of a migration from a rather poorly designed structure to a far more normalized one (where ID's are int's instead of text GUIDs for one thing).

Server is MS SQL Server 2008.

Any and all help would be very much appreciated. Thanks.
Argh.. Stupidity reigns supreme. In response to Aaron's comment (which deserved an up-vote for stating the obviously overlooked), I traced back through the nested stored procedures. Turns out there was an ALTER TABLE that was adding the columns with NULL values from some old legacy script that was an attempt at trying to do what the INSERT INTO was able to accomplish all along.

@Aaron, thanks for stating the obvious which lead me to check the code path to this point.