Weird INSERT INTO SELECT Results

Hi all,

I am having a weird problem occur when trying to insert missing records based on an INSERT INTO...SELECT statement.

Basically, I am populating a temporary table with data, then inserting into a main table those records from the temporary table that aren't in the main table. In addition, I am updating those records that are in the main table.

When I call the following SQL, all of the records from the temporary table are inserted even though those records already exist. Bizzarrely the update statement works perfectly.

INSERT INTO
AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName)
SELECT
Identifier,
AffiliateID,
1,
AffiliateCategoryName,
NetworkCategoryName
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName NOT IN
(
SELECT
AffiliateCategoryName
FROM
AffiliateCategories
)


I am not sure why it is doing this and it's driving me crazy. If anyone has any ideas why this is happening, or knows of a better way to accomplish this then please let me know.

Are there some issues with string comparisons going on here?

The table that populates the temp table is defined as:

CREATE TABLE [dbo].[Staging_TradeDoubler_Categories](
    [id] [int] NULL,
    [name] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [merchantName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [TDCategories_Id] [int] NULL,
    [AffiliateID] [int] NULL
) ON [PRIMARY]


Full code listing:

IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL
DROP TABLE #TempTradeDoublerCategories

CREATE TABLE #TempTradeDoublerCategories
(
Identifier INT,
AffiliateID INT,
AffiliateCategoryName VARCHAR(255),
NetworkCategoryName VARCHAR(255)
)



INSERT INTO
#TempTradeDoublerCategories (AffiliateCategoryName)
SELECT DISTINCT
CAST(merchantName AS VARCHAR(255)) AS MerchantName
FROM
Staging_TradeDoubler_Categories
ORDER BY
MerchantName



UPDATE
#TempTradeDoublerCategories
SET
#TempTradeDoublerCategories.Identifier = Staging_TradeDoubler_Categories.id,
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Categories.AffiliateID,
#TempTradeDoublerCategories.NetworkCategoryName = Staging_TradeDoubler_Categories.[name]
FROM
Staging_TradeDoubler_Categories
WHERE
Staging_TradeDoubler_Categories.merchantName = #TempTradeDoublerCategories.AffiliateCategoryName



UPDATE
#TempTradeDoublerCategories
SET
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.AffiliateID
FROM
Staging_TradeDoubler_Affiliates
WHERE
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.TradeDoublerAffiliateID

Print 'Inserting records'

INSERT INTO
AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName)
SELECT
Identifier,
AffiliateID,
1,
AffiliateCategoryName,
NetworkCategoryName
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName NOT IN
(
SELECT
AffiliateCategoryName
FROM
AffiliateCategories
)

Print 'Updating records'

UPDATE
AffiliateCategories
SET
AffiliateCategories.AffiliateCategoryName = #TempTradeDoublerCategories.AffiliateCategoryName,
AffiliateCategories.NetworkCategoryName = #TempTradeDoublerCategories.NetworkCategoryName,
DateModified = GETDATE()
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName IN
(
SELECT
AffiliateCategories.AffiliateCategoryName
FROM
AffiliateCategories
)



PRINT 'Dropping table'


IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL
DROP TABLE #TempTradeDoublerCategories