Archive for July 4th, 2009
From Stack Overflow:
I want to make sure I’m not inserting a duplicate row into my table (i. e. only
All my fields allow
NULL‘s as I’ve decided
NULLto meanall values.
NULL‘s, the following statement in my stored procedure can’t work:IF EXISTS ( SELECT * FROM MY_TABLE WHERE MY_FIELD1 = @IN_MY_FIELD1 AND MY_FIELD2 = @IN_MY_FIELD2 AND MY_FIELD3 = @IN_MY_FIELD3 AND MY_FIELD4 = @IN_MY_FIELD4 AND MY_FIELD5 = @IN_MY_FIELD5 AND MY_FIELD6 = @IN_MY_FIELD6 ) BEGIN goto on_duplicate END
NULL = NULLis not true.
How can I check for the duplicates without having an
IF ISNULLstatement for every column?
First of all, can’t help being a smartass and tell that a
UNIQUE index should be defined over these columns.
SQL Server does index
NULL values and they are first class values as long as uniqueness is concerned, so creating this index will manage the duplicates automatically.
Now, back to business. Of course there are cases when creating such an index is not acceptable.
In these cases a very nice but little known operator
INTERSECT comes handy.
This operator takes two resultsets with the same layout and finds rows common to them (eliminatnig duplicates), as SQL Server‘s understands common: each value in each column should be either equal to one in the other row or they both should be
Let’s create a sample table:
Read the rest of this entry »