TSQL - Distinct 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?

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

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.

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?