Solved

Ignore Error in a store procedure and continue

Posted on 2010-08-20
5
301 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 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