Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Procedure

Author

Topic

proguy
Starting Member

4 Posts

Posted - 2009-09-14 : 07:37:27

I have the following Error

Msg 2627, Level 14, State 1, Procedure addSubbom, Line 11
Violation of PRIMARY KEY constraint 'PK_OrderJobDetails'. Cannot insert duplicate key in object 'dbo.OrderJobDetails'.

from the following Procedure
ALTER PROCEDURE [dbo].[addSubbom]

AS
BEGIN
INSERT INTO [tank2.0].dbo.OrderJobDetails
( [OrderJobDetailID]
,[JobID]
,[ItemID]
,[ItemNo]
,[SelectedItemID]
,[ItemDescription]
,[OptionA]
,[OptionAValue]
,[Qty]
,[Jobtype]
,[Operation]
,[SelectedItemCode]
,[QtyOrdered]
,[OperationName] )

SELECT 1,dbo.OrderJobDetails.JobID, dbo.OrderJobDetails.ItemID, dbo.OrderJobDetails.ItemNo, dbo.ItemDetail.SelectedItemID, ItemHeaded_1.Description,
dbo.ItemDetail.SelectedOptionIDA, dbo.ItemDetail.OptionAValue, dbo.ItemDetail.Qty, ItemHeaded_1.ItemTypeID, dbo.OrderJobDetails.Operation,
ItemHeaded_1.ItemCode, dbo.OrderJobDetails.QtyOrdered, dbo.OrderJobDetails.OperationName
FROM dbo.ItemHeaded AS ItemHeaded_1 RIGHT OUTER JOIN
dbo.ItemDetail ON ItemHeaded_1.ItemID = dbo.ItemDetail.SelectedItemID RIGHT OUTER JOIN
dbo.ItemHeaded ON dbo.ItemDetail.ItemID = dbo.ItemHeaded.ItemID RIGHT OUTER JOIN
dbo.OrderJobDetails ON dbo.ItemHeaded.ItemID = dbo.OrderJobDetails.SelectedItemID
WHERE (dbo.ItemHeaded.ItemTypeID = 2)
ORDER BY dbo.OrderJobDetails.JobID, dbo.OrderJobDetails.ItemNo
END

I know what its cause is "OrderJobDetailID" because its got to be unique, But i dont know how to insert unique number, Cam someone help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-14 : 10:55:15

Your SELECT statement is returning a duplicate for whatever columns are in your primary key, so run the SELECT statement separately and figure out why there are multiple rows returned for the PK.

I suppose you could try DISTINCT, but it's hard to know what you want since you haven't provided much information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 10:58:35

i think the culprit is hardcoded value 1 you pass for OrderJobDetailID as i guess that's your pk. what you could do is make it identity column and dont specify it at all in insert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources