question

Yahya-3144 avatar image
0 Votes"
Yahya-3144 asked ·

Duplicate key issue on insert

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 
sql-server-transact-sql
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


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

0 Votes 0 ·

Hi

No luck.

Regards

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

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

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi

Thanks. This worked.

Regards

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

Hi @Yahya-3144

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.


· 8 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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


0 Votes 0 ·

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

0 Votes 0 ·

I am preparing some sample anonymous data. Will post shortly.

0 Votes 0 ·

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

Echo

0 Votes 0 ·

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
0 Votes 0 ·

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
0 Votes 0 ·

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'.


0 Votes 0 ·
Show more comments
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ·

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


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

Thanks

Regards

0 Votes 0 ·