Cannot insert duplicate key row in object....
-
Hi all, I hope some one can help me.
I have a stored procedure which is inserting data in a local table from a table on a linked server. I am getting the below error:
Msg 2601, Level 14, State 1, Procedure "Procedure Name", Line 10
Cannot insert duplicate key row in object "table" with unique index "INDEX". The duplicate key value is ("VALUE").
The statement has been terminated.
My SP looks like this:
TRUNCATE TABLE [DATBASE].[dbo].
SET IDENTITY_INSERT [DATBASE].[dbo]. ON
INSERT INTO [DATBASE].[dbo].
([VALUE1],
[VALUE2]
,[etc......])
SELECT [VALUE1],
[VALUE2]
,[etc......]
FROM [LINKED SERVER].[DATABASE].[dbo].
The table on my local server looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].(
[value1] [int] IDENTITY(1,1) NOT NULL,
[value2] [nchar](30) NOT NULL,
[value3] [int] NOT NULL,
[value4] [nvarchar](3) NOT NULL,
[value5] [nvarchar](5) NOT NULL,
[value6] [int] NOT NULL,
[value7] [nvarchar](20) NOT NULL,
[value8] [datetime] NOT NULL,
[value9] [nvarchar](10) NULL,
[value10] [int] NULL,
[value11] [datetime] NULL,
[value12] [int] NULL,
CONSTRAINT [PK_"blah"] PRIMARY KEY CLUSTERED
(
[value1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Any help would be appreciated.
-
it would seem like the value is duplicated on the linked server side
the below should bring back any values where the value is duplicated on the linked server
select value from linked_server.database.schema.table
group by value
having count(value) > 1
-
I was going to say the same thing. Check the data on the other server. That must be where the duplicate is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
ok, i have ran
select value2 from [linjed server].[database].[dbo].
group by value having COUNT(value2) > 1
this cam back with 24689 rows, so yes, there are duplicate values already in the linked server database.
what gets me is that value2 isnt the PK and so doesnt need to be unique, so why does it care that its putting the same value in this column?
-
what is the definition of the index which is referenced in this error?
Cannot insert duplicate key row in object "table" with unique index "INDEX". The duplicate key value is ("VALUE").
-
Looks like everything is retyped for us to hide things, but based on what's typed, it's saying the VALUE is the problem, not the column name.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
the error says its a unique index so I am guessing that the table being inserted has a unique index on the a column or selection of columns which is preventing the insert.
-
ah, there is indeed a index which is the same as the one detailed in the error (i didnt create the database so didnt know about this. Ignorance is bliss
) The index is set to unique, non clustered with the value2 column listed in the index which is obviously why it wont allow me to import duplicate entries in the value2 column. I have checked the database on the linked server and it doesnt have this index. Ill speak to the db creator to see if this index is required or if i can remove the unique option.
Hey, you learn something new every day. thanks for your help guys.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply