CODEWITHSUNDEEP
sqlsql-server-2005stored-proceduresinsertindexing
Tim Schmelter
Tim Schmelter

Reputation: 460108

Unique index error on INSERT with SELECT and NOT EXISTS check

I'm getting following error if i'm running a stored-procedure

Cannot insert duplicate key row in object ‘dbo.tabTac’ with unique index ‘IX_tabTac’.

Unique Index IX_tabTac: 

CREATE UNIQUE NONCLUSTERED INDEX [IX_tabTac] ON [dbo].[tabTAC] 
(
    [TAC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

Table tabTac:

CREATE TABLE [dbo].[tabTAC](
    [idTAC] [int] IDENTITY(1,1) NOT NULL,
    [TAC] [char](8) NOT NULL,
    [fiModel] [int] NOT NULL,
 CONSTRAINT [PK_tabTac] PRIMARY KEY CLUSTERED 
(
    [idTAC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tabTAC]  WITH NOCHECK ADD  CONSTRAINT [FK_tabTac_modModel] FOREIGN KEY([fiModel])
REFERENCES [dbo].[modModel] ([idModel])
GO
ALTER TABLE [dbo].[tabTAC] CHECK CONSTRAINT [FK_tabTac_modModel]

Stored-Procedure InsertTacsFromClaims:

CREATE PROC [dbo].[InsertTacsFromClaims]
with execute as Owner
AS
BEGIN
    INSERT INTO tabTac
        select substring(t.SSN_Number,1,8)as TAC
        ,m.idModel as fiModel
        from tabData t 
        inner join modmodel m 
            ON t.fimodel=m.idmodel 
        WHERE  t.fiproducttype=1
        and m.idModel>1
        and not exists(
            select fiModel from tabTac WHERE TAC=substring(t.SSN_Number,1,8)
        )
        GROUP BY substring(t.SSN_Number,1,8),m.idModel
END 

RETURN @@ROWCOUNT;

I would have thought that i have prevented this error with:

and not exists(
            select fiModel from tabTac WHERE TAC=substring(t.SSN_Number,1,8)
          )

Edit:

@Sparkys second query(ModelName added) returns following:

TAC         fiModel    ModelName
01233300    777        U5A
01238300    771        W20I
01238300    784        W20
35427603    720        C903
35773704    781        E15I
35905104    451        W595
35946804    793        W150I
35959004    813        ST18I

Now it's obvious that one TAC in tabData(first 8 chars of SSN_Number)has not a distinct model but can be linked to multiple models. That causes the error because in tabTac the TAC must be unique.

Thanks in advance

Upvotes: 1

Views: 883

Answers (1)

Sparky
Sparky

Reputation: 15075

Check to see if the SSN_Number is duplicated in the TabData table or if the join to modModel produces duplicates.

select substring(t.SSN_Number,8),count(*)
from tabData
groub by substring(t.SSN_Number,8)
having count(*) > 1

and

  select substring(t.SSN_Number,1,8)as TAC
        ,m.idModel as fiModel
        from tabData t 
        inner join modmodel m 
            ON t.fimodel=m.idmodel 
        WHERE  t.fiproducttype=1
        and m.idModel>1
        GROUP BY substring(t.SSN_Number,1,8),m.idModel
        HAVING count(*) > 1

If either query returns duplicate SSN_numbers, you'll get the error. Your code only checks that you don't add a SSN number from the table if it already exists in the table you are trying to populate, but doesn't account for potential dupes in the data already

There are several approaches to only get the most recent fiModel code, here is one example:

select SSN_Number,fiModel 
FROM tabData td1
JOIN
(  -- This will get the latest date from tabData
select SSN_Number,max(received_date) as TheLastestDate
FROM tabData td
LEFT JOIN tabTac tc on tc.TAC = substring(TD.SSN_number,1,8) 
                       and tc.fiModel = td.fModel
WHERE tc.fiModel is NULL
GROUP BY SSN_Number) xx ON xx.SSN_number=td1.ssn_number 
                           and td1.received_date = xx.TheLatestDate

Note that if you have multiple fiModels on the same date, the above won't handle it because it does not know how to distinguish between which to use. Hope this points you in the right direction.

Upvotes: 3

Related Questions