Primary key constraint problem on insert

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

SQL Server 2008 Forums

Transact-SQL (2008)

Primary key constraint problem on insert

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2010-08-12 : 13:55:57

I get the following error when trying to insert 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.

The table has a primary key consisting of STORES_CODE, PART_ID, STOCK_LOCATION. I validate the records and they are not
duplicates. I have even tried with one record where I know it doesn't exist in the target table.

Here is the code that causes the problem:

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

I even validate what is suppose to go into the MLFML table by running the following query. I get no results, so I should get all records inserted into MLFML.

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)

And of course, if I remove one of the conditions (STOCK_LOCATION) I will get many records returned.
Any help would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 14:04:47

try:-


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)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

texassynergy
Starting Member

26 Posts

Posted - 2010-08-12 : 14:18:24

Tried your solution. Still received the same error message.

texassynergy
Starting Member

26 Posts

Posted - 2010-08-12 : 14:22:54

I must apologize. Sorry. It did work. I had only cut and pasted your Where clause. After reviewing, I had noticed the From clause had changed as well. Made the corrections and it worked. Much appreciate it. You are awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 14:27:24

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources