Solved

Ignore Error in a store procedure and continue

Posted on 2010-08-20
5
299 Views
Last Modified: 2012-05-10
I have a stored procedure that insert to a table and I have creadted a primary key. I want to ignore the error Violation of Primary Key - cannot isert duplicate key.

If the record already exist then just continue with the rest of the procedure.
0
Comment
Question by:tvae
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:aamsoh
ID: 33488471
you can handel the errors like this,


DECLARE @Error INT

-- DO Stuff
UPDATE client SET cliTypCd = (SELECT cliTypCd FROM client T WHERE T.link = @throwLink) WHERE link = @keepLink
SET @Error = @@ERROR

IF @Error <> 0 AND @Error <> 30002
BEGIN
      -- Handle Error
END
-- Else Ignore
0
 

Author Comment

by:tvae
ID: 33488642
aamsoh,

What is this step doing?
UPDATE client SET cliTypCd = (SELECT cliTypCd FROM client T WHERE T.link = @throwLink) WHERE link = @keepLink

Do i need to put replace anything in there?
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 250 total points
ID: 33488656
Please check the attached script

Raj
-- Case #1

-- Copying bulk data from Table1 to Table2

INSERT INTO Table2

	SELECT * FROM Table1

	WHERE ID NOT IN (SELECT ID FROM Table2)

	-- AND (if any where conditions add here)



-- Case #2

-- Copying single row at a time

IF (SELECT COUNT(*) FROM Table2 WHERE ID = @ID) = 0

BEGIN

	INSERT INTO Table2 SELECT * FROM Table1 

						-- WHERE ... (any where condition add here)

END

Open in new window

0
 
LVL 2

Expert Comment

by:aamsoh
ID: 33488658
actully this was only a sample. you should consider on this code:
you can use this error handel with INSERT OR UPDATE quety.


SET @Error = @@ERROR

IF @Error <> 0 AND @Error <> 30002
BEGIN
      -- Handle Error
END
-- Else Ignore
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33488672
If you are inserting bulk records, first script can help. It will insert only new records based on primary key.

If you are inserting single record at a time, then you can check whether any row with the primary key value you are going to insert, already exists in the table. If not insert.

Raj
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now