TSQL - Distinct Question

Question

I have the following unique contstraint defiend on a table:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Access_AccessSOE] ON [dbo].[Access] 
(
    [AccessSOE] ASC
)

I am trying to import records to this table using the following query:

INSERT INTO Access 
  (AccessSOE, AccessName, AccessBox, AccessLocation, 
   AccessBusiness, AccessPhone, AccessFax, AccessEmail,
   LastUpdatedBy, Deleted, AccessPrimaryKey)
SELECT DISTINCT(i.AccessSOE), i.AccessName, i.AccessBox, i.AccessLocation,
       i.AccessBusiness, i.AccessPhone, i.AccessFax, i.AccessEmail,
       'Admin', 0, i.IndexNew
  FROM Access_IMPORT i
 WHERE i.AccessSOE NOT IN (SELECT a.AccessSOE FROM ACCESS a)

However the import fails. The only unique constraint on the table is the AccessSOE field, and I thought by selecting only distinct items, my query would be correct.

Can anyone provide any help?

Cannot insert duplicate key row in object 'dbo.Access' with unique index 'IX_Access_AccessSOE'. — Mick, Nov 02 '10 at 18:52
Are you inserting into a clean table? E.g. no chance for duplicates? If there's a chance for duplicates then you NOT IN (SELECT...) query might have to UNION with data from the target table iteself to avoid the dupes. — Paul Sasik, Nov 02 '10 at 18:56
@JNK: i meant the target of the import, so the not in subselect would look something like: ...FROM ACCESS UNION ...FROM Access_IMPORT — Paul Sasik, Nov 02 '10 at 19:06

score 4 · Accepted Answer · answered Nov 02 '10 at 18:53

4

First, try changing

Where i.AccessSOE not in (Select a.AccessSOE from Access a)

Into:

Where NOT EXISTS
    (SELECT * FROM Access a WHERE a.AccessSOE = i.AccessSOE)

Any NULLs here will Select a.AccessSOE from Access a cause the entire NOT IN to be false

Then, check you haven't duplicates in what you insert. DISTINCT applies to all columns BTW

SELECT COUNT(*), i.AccessSOE, i.AccessName, i.AccessBox, i.AccessLocation,
       i.AccessBusiness, i.AccessPhone, i.AccessFax, i.AccessEmail,
       'Admin', 0, i.IndexNew
  FROM Access_IMPORT i
 where NOT EXISTS
        (SELECT * FROM Access a WHERE a.AccessSOE = i.AccessSOE)
GROUP BY
      i.AccessSOE, i.AccessName, i.AccessBox, i.AccessLocation,
       i.AccessBusiness, i.AccessPhone, i.AccessFax, i.AccessEmail,
       'Admin', 0, i.IndexNew
HAVING COUNT(*) > 1

answered Nov 02 '10 at 18:53

gbn

422,506
82
585
676

+1 for `EXISTS` but, can he have a `NULL` on a column with a unique index? – JNK Nov 02 '10 at 18:55
1

@JNK With the obvious exception of primary keys, aren't `unique` and `nullable` separate constraints? – Dan J Nov 02 '10 at 18:57
@djacobson - yeah but I wasn't sure it would be allowed as a value regardless(since by definition `NULL` is a non-value...like you can't `NULL` a pk). In my experience I haven't had to use unique indexes. – JNK Nov 02 '10 at 18:59
[This question](http://stackoverflow.com/questions/191421/how-to-create-a-unique-index-on-a-null-column) has some interesting information about the concept. Sounds like it's at least somewhat implementation-dependent... – Dan J Nov 02 '10 at 19:01

score 1 · Answer 2 · answered Nov 02 '10 at 18:54

Despite the fact that the confusing DISTINCT(i.AccessSOE) syntax is legal, the DISTINCT applies across all the columns you're selecting, not just on i.AccessSOE, so you could have duplicates of those as long as the combination of all the selected columns is unique.

score 0 · Answer 3 · answered Nov 02 '10 at 18:54

The SELECT DISTINCT returns rows where all columns combine to create a distinct row. Your unique constraint enforces the rule that AccessSOE must be unique. Can you check your data to see if there are duplicate values fro AccessSOE, where other columns make the row distinct?

TSQL - Distinct Question

3 Answers3