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:


1     abc123
2     abc345
3     abc456
4     abc678


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
ON A.Text_GUID = B.Text_GUID;

I expect this:


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:


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:


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.

  • 3
    Can you explain how and where you are running this query? SQL Server doesn't just create columns, so I suspect this is the work of some other tool or designer you are using. – Aaron Bertrand Nov 20 '13 at 2:59
  • @Aaron - Stored procedure running from SSMS. There are a series of INSERT INTO... SELECT FROM calls preceding. But it's quite a nested query. I am backtracking to see what may be causing besides this call (which is what baffled me) – Hooligancat Nov 20 '13 at 6:26
  • I actually appreciate the "off-topic" status. The "can't see the woods for the trees" problem got the better of me before posting and it's good to see you guys on the ball for keeping me honest :-) Thanks again to @Aaron for your help! – Hooligancat Nov 21 '13 at 16:45

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.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.