Duplicate key issue on insert

Y a h y a 416 Reputation points
2021-03-05T00:11:36.237+00:00

Hi

I have a TblSource table (from a linked db) that I need to insert into an equivalent TblDest table with ids so I need to check for duplicate ids during insert. TblDest is a child to tblMaster so during insert I also need to check if a parent row exists in tblMaster. I have tried below code at the end but it seems the two EXISTS/ NOT EXISTS clauses don’t work as needed. I get partial success if I change the order of the clauses, not sure why.

Currently I am getting below error;

Violation of PRIMARY KEY constraint 'PK_TblDest'. Cannot insert duplicate key in object 'dbo.TblDest'. The duplicate key value is (8).

How can I achieve a successful inset? Thanks.

Regards 

SET IDENTITY_INSERT tbldest ON

go

INSERT INTO tbldest(id,masterid)
SELECT tblsource.id,tblsource.[master id]
FROM   [LinkedDB]...tblsource
WHERE  ( NOT EXISTS (SELECT tbldest.id
                     FROM   tbldest
                            INNER JOIN [LinkedDB]...tblsource
                                    ON tbldest.id = tblsource.id) )
       AND ( EXISTS (SELECT tblmaster.id
                     FROM   tblmaster
                            INNER JOIN [LinkedDB]...tblsource
                                    ON tblmaster.id = tblsource.[master id]) )

go

SET IDENTITY_INSERT tbldest OFF

go
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Viorel 112.1K Reputation points
    2021-03-05T09:52:14.707+00:00

    Try a modified statement: 

    insert into TblDest(ID, MasterID)
select  s.ID, s.[Master ID]
from    TblSource s
where   not exists (select * from TblDest where id = s.ID)
and     exists (select * from TblMaster where id = s.[Master ID])

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-03-05T02:49:44.247+00:00

    Hi @Y a h y a ，

    This error message indicates that the value you are trying to insert has a duplicate primary key.You may be using the wrong column as the primary key. The value of the primary key column must be unique and cannot appear multiple times.

    You can try to create a composite primary key.Please refer to:
    Create Foreign Key Relationships

    If this does not solve your problem, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …). So that we’ll get a right direction and make some test.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Guoxiong 8,126 Reputation points
    2021-03-05T15:16:17.25+00:00

    Since the table [dbo].[TblDest] has the foreign key which is referencing the table [dbo].[TblMaster], you can not insert data into the column [dbo].[TblDest].[MasterID] if it does not exist in the table [dbo].[TblMaster]. Try this:

    SET IDENTITY_INSERT TblDest ON
GO

INSERT INTO [dbo].[TblDest]([ID], [MasterID])
SELECT TblSource.[ID], TblSource.[Master ID]
FROM [dbo].[TblSource]
INNER JOIN [dbo].[TblMaster] ON TblMaster.[ID] = TblSource.[Master ID]
WHERE NOT EXISTS (
    SELECT 1 FROM [dbo].[TblDest] WHERE [ID] = TblSource.[ID] AND [MasterID] = TblSource.[Master ID]
)
GO

SET IDENTITY_INSERT TblDest OFF
GO