Duplicate Error when inserting unique non duplicate values T-SQL

Question

Here is my other mistake I can't fight:

I am trying to add (INSERT INTO sql statement) a unique records from the WHID field (ClientEpisode table) into WHID field (EHREpisode table):

      INSERT INTO 
                  [WH].[Fact].EHREpisode ([WHID])
         SELECT
               [HP].[bhcms5].ClientEpisode.WHID
         FROM
             [HP].[bhcms5].ClientEpisode

Both of the WHID fields are unique (non duplicated) in both tables, and between each others, but I keep having an error:

Plz, see the error message, in bigger letters:

"Cannot insert duplicate key row in object 'Fact.EHREpisode' with unique index 'IX_EHREpisode'. The duplicate key value is (NULL, NULL>). The statement has been terminated."

Below are my tables structures:

EHREpisode:

ClientEpisode:

score 3 · Answer 1 · answered Dec 07 '18 at 06:00

3

The error message seems to be saying something about NULL values being the culprit (your screen capture is hard to read). So, you may try excluding NULL values from being inserted:

INSERT INTO [WH].[Fact].EHREpisode ([WHID])
SELECT [HP].[bhcms5].ClientEpisode.WHID
FROM [HP].[bhcms5].ClientEpisode
WHERE [HP].[bhcms5].ClientEpisode.WHID IS NOT NULL;

As an alternative, consider using an index on WHID which ignores NULL values:

CREATE UNIQUE NONCLUSTERED INDEX idx_col1
ON [HP].[bhcms5].ClientEpisode (WHID)
WHERE WHID IS NOT NULL;

answered Dec 07 '18 at 06:00

Tim Biegeleisen

502,043
27
286
360

Unfortunately, none of the above helped :( It created unique nonclustered index (from your 2nd advice), but I am still getting the same mistake: "Cannot insert duplicate key row in object 'Fact.EHREpisode' with unique index 'IX_EHREpisode'. The duplicate key value is (, ). The statement has been terminated." I will update my original post with the error message (bigger font) – Hell-1931 Dec 07 '18 at 07:18
Please tell us what this index is? Show the definition if possible. – Tim Biegeleisen Dec 07 '18 at 07:21

score 1 · Accepted Answer · answered Dec 07 '18 at 11:30

The error is very clear: There is a unique index called IX_EHREpisode with such columns that your query tries to insert the value (null,null) two times.

You have provided us a set of SSMS GUI pictures that are not really relevant to this. Instead, the way to use the GUI would be:

Go to the "Object explorer" at the left of SSMS
Find the table "EHREpisode" you are interested in
Open "Indexes". You should find IX_EHREpisode here. Open it.
Inside you will see the columns included in this index. There will be a couple of these that would both take NULL value if your query executed

Thus, you will either have to modify the index, or re-think your query.

Duplicate Error when inserting unique non duplicate values T-SQL

2 Answers2