Cannot insert duplicate key row in object

[Bloke152]

Hi guys,

getting with error on a script
'Cannot insert duplicate key row in object 'CUBE_DIM_PC_PART_TAB' with unique index 'ixCubeDimPcPart_ItemKey'.
The statement has been terminated.'

This is the result of this script
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CUBE_DIM_PC_PART_TAB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CUBE_DIM_PC_PART_TAB]
-- CREATE
CREATE TABLE [dbo].[CUBE_DIM_PC_PART_TAB] (
[item_key][varchar] (255) NOT NULL ,[part_desc] [varchar] (255)NOT NULL ,[part_lvl_display][varchar] (255) NOT NULL ,
[part_type][varchar] (25)NOT NULL
) ON [PRIMARY]

-- INDEX
CREATE UNIQUE CLUSTERED INDEX ixCubeDimPcPart_ItemKey ON [dbo].[CUBE_DIM_PC_PART_TAB] ([Item_Key])
-- LOAD
INSERT INTO [hydra].[dbo].[CUBE_DIM_PC_PART_TAB]
SELECT v1.item_key,
LEFT(v1.part_desc, 255),
LEFT(v1.part_lvl_display,255),
v1.part_type

FROM hydra.dbo.vwCube_Dim_PC_Part v1
ORDER BY v1.item_key

I have looked on the net and there appears fo be a few solutions, none of which are the problem i am having, anyone got any ideas?

 

[gmmastros]

First...

I would suggest that you make the item_key column a primary key. Primary keys are implemented through unique indexes, so you would be able to remove the CREATE INDEX part. In reality, this won't make much difference, but it's what I would do.

[tt][blue]
CREATE TABLE [dbo].[CUBE_DIM_PC_PART_TAB] (
[item_key][varchar] (255) NOT NULL [!]Primary Key[/!],[part_desc] [varchar] (255)NOT NULL ,[part_lvl_display][varchar] (255) NOT NULL ,
[part_type][varchar] (25)NOT NULL
) ON [PRIMARY]
[/blue][/tt]

If I had to guess, I would say that it's the INSERT INTO part that is causing the problem. Apparently, you are inserting data from a view. If I had to guess, I would say that there are duplicate item_key's returned. You can test this by running the following query.

SELECT v1.item_key, Count(*)
FROM   hydra.dbo.vwCube_Dim_PC_Part v1
Group By item_key
Having Count(*) > 1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bloke152]

that was my initial thought but there are no duplicate item_keys, there arent even any null ones to blame it on! I did read somewhere that if you have over a certain amount of rows this can occur but we're not even close to that either. thanks for your reply