Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

571 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