Re: Wierd results

From: VikramKamath (vikramvkamath_at_hotmail.com)
Date: 05/25/04 

Date: Tue, 25 May 2004 04:01:04 -0700

This works as long as i have all the 4 rows common in @v_DIM and @v_DimTable.

but if i put only one row in the @v_DimTable, thats the first one. then your query puts all the other rows not present in @v_DimTable.

But i wan only the row with the same [NAME] to be inserted.

But if u use the following work around it works in all case

INSERT INTO @v_DimTable
(
    [URN],
    [NAME]
)
SELECT dn.[URN] , dn.[NAME]
FROM @v_DIM AS dn
JOIN @v_DimTable AS dim ON dn.[NAME] = dim.[NAME]
WHERE dn.[URN] NOT IN (SELECT [URN] FROM @v_DimTable)

My question is why does the original query not work when we have the same rows in both the tables.