MERGE statement Error help please.

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)

MERGE statement Error help please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-10 : 14:23:06

I am getting this error when I execute the Merge statement below. I need a pairs of eyes to see what wrong.

Thanks in advance.

--Error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_BorrData_1'. Cannot insert duplicate key in object 'dbo.BorrData'.
The statement has been terminated.

-----------------------------------------------------------------------------

IF OBJECT_ID('BorrData ', 'u') IS NOT NULL
DROP TABLE BorrData
GO
CREATE TABLE BorrData
(
LoanNum VARCHAR(10) NULL,
BorrLast VARCHAR(20) NULL,
BorrFICO CHAR(10) NULL
)
GO

--Testing. Not working...

merge BorrData as TargetTable
using ( select LoanNum from BorrData ) as SourceTable on TargetTable.LoanNum = SourceTable.LoanNum

when matched then
update set
LoanNum = '123333',
BorrFICO = '659'

when not matched then
insert
(LoanNum, BorrFICO)
values
('123333', '659');
go

select * from BorrData where LoanNum = '123333'

-- DELETE BorrData WHERE LoanNum = '123333'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-10 : 14:46:18

You aren't showing us all the code. For instance, how can there be a Primary Key violation when there is no primary key.

The code you posted doesn't generate any errors.

But I'll guess that you're creating a PK on LoanNum, then trying to update it...

Show us all the code.

BruceT
Yak Posting Veteran

78 Posts

Posted - 2010-12-10 : 16:09:07

Well, assuming you've added a primary key on loannum then given the example, if you have 2 loannums that are not matched then the first will insert '123333','659' and the second will try to insert '123333','659' thus resulting in a primary key violation on the second insert. Actually any combination of events would cause this. Two matched rows, first updates to '123333' the second tries to update to '123333'. One unmatched and one matched....you get the drift.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-10 : 18:53:14

See if this makes more sense as an example. I couldn't test as this machine doesn't have 08 on it yet..


CREATE TABLE BorrData
(
LoanNum VARCHAR(10) NULL,
BorrLast VARCHAR(20) NULL,
BorrFICO CHAR(10) NULL
)
GO

Create Table NewData (
LoanNum VARCHAR(10) NULL,
BorrLast VARCHAR(20) NULL,
BorrFICO CHAR(10) NULL)

go

Insert into BorrData
Select '123333','Smith','659' UNION
Select '123334','Jones','651' UNION
Select '123335','Johnson','585'

GO

Insert into NewData
Select '123333','Smith','661' UNION
Select '123335','Johnson','585' UNION
Select '123339','Datguru', '820' 


Select * FROM BorrData

GO

MERGE BorrData as TARGET
USING ( select LoanNum,BorrLast,BorrFico
		from NewData ) as SOURCE (LoanNumb,BorrLast,BorrFico)
 on TARGET.LoanNum = SOURCE.LoanNum 

WHEN MATCHED THEN
	update set BorrFICO = SOURCE.BorrFico

WHEN NOT MATCHED THEN
	insert
		(LoanNum,BorrName BorrFICO)
      values
		SOURCE.Loannum,SOURCE.BorrName,SOURCE.BorrFico;
go

Select * FROM BorrData

You should see the replaced FICO for 'Smith' and the added record for dataguru and Johnson in the last result.

There are a lot of options with Merge..but it takes getting used to.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources