SQL Server Try Catch?

Posted on 2005-03-22
Medium Priority
Last Modified: 2010-05-18
I have a query which inserts data it selects from another table. The table it is inserting to has a unique contraint on one of the fields to keep duplicates out of the table... Problem is that when I run this query and a dup occurs the entire query terminates. Is there a way to tell SQL Server to not stop on this event, and just skip that record and continue?

insert into openers
select * from list99 where id IN (select uid from topeners where listid=13)

Error Message:
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'IX_openers'. Cannot insert duplicate key in object 'openers'.
The statement has been terminated.

Worth 500 points.

Question by:richardsimnett
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

alexandermancera earned 2000 total points
ID: 13606772
You can't execute one statement parcially.
You need modify your instruction and be sure no error ocurre.

For example, you can try:

insert into openers
select * from list99 where id IN (select uid from topeners where listid=13)
and id not in (select id from openers)
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13606841
Try it this way:

Insert      openers
Select      *
From      list99 l
      Inner Join (
            Select      uid
            From      topeners
            Where listid=13) t On l.id = t.uid
      Left Join operners o On l.KeyField = o.KeyField
where      o.KeyField Is Null
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13606852
Replace "KeyField" with the column you are using  "to keep duplicates out of the table"

Author Comment

ID: 13607108
I tried your query and it quit with failure. The following error was produced:
Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Not sure what that means, but the other query worked perfectly.


Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

764 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