Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Primary key constraint violation problem using Insert in SQL Server Management Studio 2008

I am getting the following error when trying to insert new records into a table.
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'MLFML_PK'. Cannot insert duplicate key in object 'dbo.MLFML'.
The statement has been terminated.

Here is my code:

declare @TODAY varchar(10)
SET @TODAY = (SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY])
 
 
INSERT INTO MLFML (BOOK_QTY, CREATED_BY, DATE_CREATED, DATE_LAST_ACT, DATE_LAST_ISSUE, DATE_LAST_PHY, DATE_LAST_PRINT,
DATE_LAST_RECPT, MLFML_USER_1,  MLFML_USER_2, MLFML_USER_3, MLFML_USER_4, ON_HAND_QTY, OPERATOR_ID, PART_ID, PHYSICAL_QTY, 
STOCK_LOCATION, STORES_CODE, DATE_LAST_UPDT,  TIME_LAST_UPDT,  DELETE_FLAG) 

SELECT 0, 'SYS.DM', GETDATE(), @TODAY, NULL, @TODAY, NULL, @TODAY, 0, 0, '', '', PI.QUANTITY, 'SYS.DM', PI.PART_ID, 0, PI.TO_LOCATION, 
PI.STORES_CODE,  convert(char(11),getdate(),101), convert(char(5),getdate(),108), 'N'

FROM PISTOCKTRANSFER PI, MLFML ML
WHERE ((PI.STORES_CODE <> ML.STORES_CODE) AND (PI.PART_ID <> ML.PART_ID) AND (PI.TO_LOCATION <> ML.STOCK_LOCATION))

Open in new window


The primary key is STORES_CODE, PART_ID, STOCK_LOCATION

I try and validate the records by using the following select:

 
SELECT * FROM MLFML ML
JOIN PISTOCKTRANSFER PI
ON PI.PART_ID = ML.PART_ID
WHERE (ML.STORES_CODE = PI.STORES_CODE) AND (ML.STOCK_LOCATION = PI.TO_LOCATION)

Open in new window


Of course I get 0 results from the above SELECT statement, however I get the primary key constraint when trying to insert.  
Please help
0
rbmacct
Asked:
rbmacct
  • 3
  • 2
1 Solution
 
CboudrozCommented:
you need to used left join or if not exists

 
-- whit a left join
SELECT 
	* 
FROM 
	PISTOCKTRANSFER PI
	left join MLFML ML	
		ON PI.PART_ID = ML.PART_ID
			and PI.STORES_CODE = ML.STORES_CODE
			and PI.STOCK_LOCATION = ML.STOCK_LOCATION
WHERE 
	ml.PART_ID is null


-- whit if not exists
SELECT 
	* 
FROM 
	PISTOCKTRANSFER PI
WHERE 
	not exists 
	(
			select 
				*
			from 
				MLFML ML	
			where 
				PI.PART_ID = ML.PART_ID
				and PI.STORES_CODE = ML.STORES_CODE
				and PI.STOCK_LOCATION = ML.STOCK_LOCATION
	)

Open in new window

0
 
rbmacctAuthor Commented:
The problem is with the Insert, not the Select.

However, for information only, I did use the exact where clause with the "not exists" in my insert statement and still got the same error message.
0
 
CboudrozCommented:
--The problem is with the Insert, not the Select.
I know the select I sent was to put in the insert statement.

--However, for information only, I did use the exact where clause with the "not exists" in my insert statement and still got the same error message.

2 possibility,
1- your primary key is not STORES_CODE, PART_ID, STOCK_LOCATION
2- you have a trigger on the table MLFML  trying to insert something that create duplicate

 
declare @TODAY varchar(10)
SET @TODAY = (SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY])
 
 
INSERT INTO MLFML (BOOK_QTY, CREATED_BY, DATE_CREATED, DATE_LAST_ACT, DATE_LAST_ISSUE, DATE_LAST_PHY, DATE_LAST_PRINT,
DATE_LAST_RECPT, MLFML_USER_1,  MLFML_USER_2, MLFML_USER_3, MLFML_USER_4, ON_HAND_QTY, OPERATOR_ID, PART_ID, PHYSICAL_QTY, 
STOCK_LOCATION, STORES_CODE, DATE_LAST_UPDT,  TIME_LAST_UPDT,  DELETE_FLAG) 
-- whit if not exists
SELECT 
	* 
FROM 
	PISTOCKTRANSFER PI
WHERE 
	not exists 
	(
			select 
				*
			from 
				MLFML ML	
			where 
				PI.PART_ID = ML.PART_ID
				and PI.STORES_CODE = ML.STORES_CODE
				and PI.STOCK_LOCATION = ML.STOCK_LOCATION
	)

Open in new window

0
 
rbmacctAuthor Commented:
Solution found.  Solution is as follows:

INSERT INTO MLFML (BOOK_QTY, CREATED_BY, DATE_CREATED, DATE_LAST_ACT, DATE_LAST_ISSUE, DATE_LAST_PHY, DATE_LAST_PRINT, DATE_LAST_RECPT, MLFML_USER_1,
MLFML_USER_2, MLFML_USER_3, MLFML_USER_4, ON_HAND_QTY, OPERATOR_ID, PART_ID, PHYSICAL_QTY, STOCK_LOCATION, STORES_CODE, DATE_LAST_UPDT,
TIME_LAST_UPDT,  DELETE_FLAG)

SELECT 0, 'SYS.DM', GETDATE(), @TODAY, NULL, @TODAY, NULL, @TODAY, 0, 0, '', '', PI.QUANTITY, 'SYS.DM', PI.PART_ID, 0, PI.TO_LOCATION, PI.STORES_CODE,
convert(char(11),getdate(),101), convert(char(5),getdate(),108), 'N'

FROM PISTOCKTRANSFER PI
WHERE NOT EXISTS (SELECT 1 FROM MLFML WHERE (STORES_CODE = PI.STORES_CODE) AND (PART_ID = PI.PART_ID) AND (STOCK_LOCATION = PI.TO_LOCATION)




0
 
rbmacctAuthor Commented:
Found solution elsewhere.  Updating posting with a solution that works.  Tried to award some points to Cboudroz but the system did not let me.  It was all or nothing.  It would not let me award partial points without awarding all the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now