store procedure's problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

store procedure's problem

Author

Topic

hong_ma
Starting Member

37 Posts

Posted - 2006-08-01 : 10:32:44

I have a store procedures. which is below. when run it, I got a erroe message. which is "Cannot insert duplicate key row in object 'adBorrowertoRelationship' with unique index 'UniqueRelationshiptoBorrowerIDs'.
The statement has been terminated.". How can I change it? Thanks.

CREATE PROCEDURE dbo.asp_insert_RelNum_to_Dashboard (@RelName varchar(100), @CDBRelNum varchar(50), @Enumber char(7)) AS

INSERT INTO dbo.adBorrowerRelationship
(RelName, CDBRelNum, Enumber)
VALUES (@RelName, @CDBRelNum, @Enumber);

insert into dbo.adBorrowertoRelationship
(RelationshipID, BorrowerID, CreatedBy, CreatedDate)
(SELECT adBorrowerRelationship.RelationshipID, adBorrower.BorrowerID, @Enumber, getdate()
FROM adBorrower INNER JOIN
CDB.dbo.tblCustomer ON adBorrower.CustNum = CDB.dbo.tblCustomer.CustNum
INNER JOIN
adBorrowerRelationship ON CDB.dbo.tblCustomer.RelNum = adBorrowerRelationship.CDBRelNum
where dbo.adBorrowerRelationship.CDBRelNum = @CDBRelNum)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-01 : 10:40:01

The table adBorrowertoRelationship has a column which accepts unique IDs only.
This error is due to trying to insert a record into the table adBorrowertoRelationship, by violating that constraint.

If the table has a constraint, u should follow that.

Do u want to find which causes the error or u want to add the rows which are not making errors ?

Srinika

hong_ma
Starting Member

37 Posts

Posted - 2006-08-01 : 10:42:52

I want to add the rows which are not making errors. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-01 : 11:00:26

Then add only unique data

Madhivanan

Failing to plan is Planning to fail

hong_ma
Starting Member

37 Posts

Posted - 2006-08-01 : 11:10:41

How to do that? Thanks.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-01 : 11:48:43

understand the folloeing and customize for ur situation.


USE pubs
GO
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
   AND type = 'business')
ORDER BY pub_name
GO

change ur select query by using the above

Srinika

hong_ma
Starting Member

37 Posts

Posted - 2006-08-01 : 13:24:57

Thanks,
after I changed the query below. the error message gone, but I can not insert the data into the adBorrowertoRelationship table. Thanks.

insert into dbo.adBorrowertoRelationship
(RelationshipID, BorrowerID, CreatedBy, CreatedDate)
(SELECT adBorrowerRelationship.RelationshipID, adBorrower.BorrowerID, @Enumber, getdate()
FROM dbo.adBorrower INNER JOIN
CDB.dbo.tblCustomer ON dbo.adBorrower.CustNum = CDB.dbo.tblCustomer.CustNum INNER JOIN
dbo.adBorrowerRelationship ON CDB.dbo.tblCustomer.RelNum = dbo.adBorrowerRelationship.CDBRelNum INNER JOIN
dbo.adBorrowertoRelationship ON dbo.adBorrower.BorrowerID = dbo.adBorrowertoRelationship.BorrowerID
where dbo.adBorrowerRelationship.CDBRelNum = @CDBRelNum and not exists( select BorrowerID from dbo.adBorrowertoRelationship) )

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-01 : 13:58:18

hong_ma,

You need to add a where clause to your SELECT in the not exists:


and not exists( select BorrowerID from dbo.adBorrowertoRelationship ar where ar.borrowerid = dbo.adBorrower.borrowerID)

Ken

hong_ma
Starting Member

37 Posts

Posted - 2006-08-02 : 16:07:59

Thanks, but it still not working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources