Solved

Ignore Error in a store procedure and continue

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

726 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