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:
UID Text_GUID 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 INNER JOIN [TableB] AS B 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.