sql server, composite keys - ignoring duplicate

Is there a way to prevent sql from throwing an error when I try to save a record that already exists. I've got a composite key table for a many-to-many relationship that has only the two values, when I update a model from my application, it tries to save all records, the records that already exist throw an error Cannot insert duplicate key is there a way of having the database ignore these, or do I have to handle it in the application?

Answers


you are calling an INSERT and trying to add duplicated keys. This error is by design, and essential. The DB is throwing an exception for an exceptional and erroneous condition.

If you are, instead, trying to perform an "upsert" you may need to use a stored procedure or use the MERGE syntax.

If, instead, you don't want to UPDATE but to just ignore rows already in the table, then you need to simply add an exception to your INSERT statement... such as

....
WHERE 
    table.Key <> interting.key

Try something like this with your insert statement.

insert into foo (x,y)
select @x,@y
except
select x,y from foo

This will add a record to foo, ONLY if it is not already in the table.


You could try creating your index with the IGNORE_DUP_KEY option so that you only get a warning when you have duplicate keys rather than a true error.

The other option and possibly the better one is to use the MERGE statement rather than insert. The MERGE statement let's you do Inserts, Updates and Deletes all in one statement and sounds like it should work out well for what you are trying to do.

Last but not least, as you said fix it in your app and only insert the rows that need to be added.


Need Your Help

Dynamic content not styled in jQuery Mobile ListView with iScroll

jquery jquery-mobile cordova iscroll4

I am trying to build a Cordova/PhoneGap app with the option to "Pull Up" to load dynamic content using ajax call. The remote call runs fine and the dynamic content is added as the child elements to...

Scrolling with Multiple ListViews for Android

android listview scrollview android-linearlayout

I'm completely stumped on this one. I have three different lists that need to be displayed on the screen. It's completely possible that the lists will extend past the bottom edge of the screen, so I

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.