Duplicate key issue on insert

Question

Duplicate key issue on insert

Y a h y a 416

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

Viorel 94,761 Reputation points

2021-03-05T08:50:05.423+00:00

Do you have duplicate ID in source table? Try this: ‘select * from [LinkedDB]...tblsource group by id having count(id) > 1’.
Y a h y a 416 Reputation points

2021-03-05T09:35:02.973+00:00

Hi

No luck.

Regards

Viorel 94,761 Reputation points

2021-03-05T08:50:05.423+00:00

Do you have duplicate ID in source table? Try this: ‘select * from [LinkedDB]...tblsource group by id having count(id) > 1’.
Y a h y a 416 Reputation points

2021-03-05T09:35:02.973+00:00

Hi

No luck.

Regards

Answer 1

Viorel 94,761

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])

-- -- 706 Reputation points

2021-03-08T09:38:02.017+00:00

Hi

Thanks. This worked.

Regards

Answer 2

EchoLiu-MSFT 14,516

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.

Y a h y a 416

Hi

Sorry I should have posted DML earlier. Here is the TblDest;

CREATE TABLE [dbo].[TblDest](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [MasterID] [int] NULL,
     CONSTRAINT [PK_TblDest] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[TblDest]  WITH NOCHECK ADD  CONSTRAINT [FK_TblDest_TblMaster] FOREIGN KEY([MasterID])
    REFERENCES TblMaster ([ID])
    NOT FOR REPLICATION 
    GO

    ALTER TABLE [dbo].[TblDest] CHECK CONSTRAINT [FK_TblDest_TblMaster]
    GO

Y a h y a 416 Reputation points

2021-03-05T04:17:42.197+00:00

I have moved around EXISTS/NON EXISTS clauses and I am getting inconsistent results from EXISTS/NOT EXISTS. Currently I am getting below error. I have rebuilt indexes in case that was the issue but no luck. Is there another way to include or exclude certain rows during insert?

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TblDest_TblMaster". The conflict occurred in database "MyDB", table "dbo.TblMaster", column 'ID'.

Thanks
Y a h y a 416 Reputation points

2021-03-05T05:25:22.907+00:00

I am preparing some sample anonymous data. Will post shortly.
EchoLiu-MSFT 14,516 Reputation points

2021-03-05T07:51:21.347+00:00

You only provided the structure of one table. Could you provide the structure and sample data of all the tables involved?

Echo

Y a h y a 416

Hi

DDL below;

CREATE TABLE [dbo].[TblDest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MasterID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblMaster](
    [ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_TblMaster] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TblSource](
    [ID] [int] NULL,
    [Master ID] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TblDest]  WITH CHECK ADD  CONSTRAINT [FK_TblDest_TblMaster] FOREIGN KEY([MasterID])
REFERENCES [dbo].[TblMaster] ([ID])
GO

ALTER TABLE [dbo].[TblDest] CHECK CONSTRAINT [FK_TblDest_TblMaster]
GO

Y a h y a 416

DML below;

SET IDENTITY_INSERT [dbo].[TblMaster] ON 
GO
INSERT [dbo].[TblMaster] ([ID]) VALUES (56)
GO
INSERT [dbo].[TblMaster] ([ID]) VALUES (1038)
GO
INSERT [dbo].[TblMaster] ([ID]) VALUES (3523)
GO
INSERT [dbo].[TblMaster] ([ID]) VALUES (4855)
GO
INSERT [dbo].[TblMaster] ([ID]) VALUES (6865)
GO
INSERT [dbo].[TblMaster] ([ID]) VALUES (8022)
GO
INSERT [dbo].[TblMaster] ([ID]) VALUES (9941)
GO
SET IDENTITY_INSERT [dbo].[TblMaster] OFF
GO

INSERT [dbo].[TblSource] ([ID], [Master ID]) VALUES (1, 4855)
GO
INSERT [dbo].[TblSource] ([ID], [Master ID]) VALUES (3, 19449)
GO
INSERT [dbo].[TblSource] ([ID], [Master ID]) VALUES (4, 19449)
GO
INSERT [dbo].[TblSource] ([ID], [Master ID]) VALUES (8, 14886)
GO

Y a h y a 416

So currently I have this SQL with below error;

SET IDENTITY_INSERT TblDest ON
GO

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

SET IDENTITY_INSERT TblDest OFF
GO

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TblDest_TblMaster". The conflict occurred in database "Test", table "dbo.TblMaster", column 'ID'.

EchoLiu-MSFT 14,516 Reputation points

2021-03-09T05:46:19.97+00:00

Sorry, I didn't see your reply due to some accident. I see that your problem has been resolved, if you still have any questions, please contact me.

As shown in the figure below, the location of "Accept Answer" is located below the Answer:

The account marked as "Accept Answer" needs to be consistent with the account that asked the question.

Regards
Echo

Answer 3

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

-- -- 706 Reputation points

2021-03-08T09:38:54.647+00:00

@Viorel solution worked. How do I set as answer?

Thanks

Regards

Duplicate key issue on insert

2 additional answers

Activity