How to Ignore "Duplicate Key" Error in T-SQL (SQL Server)

How to Ignore Duplicate Key error in T-SQL (SQL Server)

Although my emphatic advice to you is to structure your sql so as to not attempt duplicate inserts (Philip Kelley's snippet is probably what you need), I want to mention that an error on a statement doesn't necessarily cause a rollback.

Unless XACT_ABORT is ON, a transaction will not automatically rollback if an error is encountered unless it's severe enough to kill the connection. XACT_ABORT defaults to OFF.

For example, the following sql successfully inserts three values into the table:

create table x ( y int not null primary key )

begin transaction
insert into x(y)
values(1)
insert into x(y)
values(2)
insert into x(y)
values(2)
insert into x(y)
values(3)
commit

Unless you're setting XACT_ABORT, an error is being raised on the client and causing the rollback. If for some horrible reason you can't avoid inserting duplicates, you ought to be able to trap the error on the client and ignore it.

Skip-over/ignore duplicate rows on insert

INSERT dbo.DataValue(DateStamp, ItemId, Value)
SELECT DateStamp, ItemId, Value 
FROM dbo.tmp_holding_DataValue AS t
WHERE NOT EXISTS (SELECT 1 FROM dbo.DataValue AS d
WHERE DateStamp = t.DateStamp
AND ItemId = t.ItemId);

INSERT IGNORE and ON DUPLICATE KEY UPDATE not working in SQL Server 2008 R2

SQL Server does not support INSERT IGNORE or ON DUPLICATE. That syntax is specific to MySQL.

If you had looked up the INSERT statement in the SQL Server manual you would have seen that.

You need to use the MERGE statement in order to update or insert.

when inserting with a select query to .mdb

I don't understand that part. If you have SQL Server you are not "inserting into a .mdb".

Are you maybe running MS Access instead? In that case the MERGE will not work either as far as I know (you would need to check the manual for MS Access for an equivalent statement)

How to ignore duplicate Primary Key in SQL?

How do I make SQL ignore the duplicate primary keys?

Under no circumstances can a transaction be committed that results in a table containing two distinct rows with the same primary key. That is fundamental to the nature of a primary key. SQL Server's IGNORE_DUP_KEY option does not change that -- it merely affects how SQL Server handles the problem. (With the option turned on it silently refuses to insert rows having the same primary key as any existing row; otherwise, such an insertion attempt causes an error.)

You can address the situation either by dropping the primary key constraint or by adding one or more columns to the primary key to yield a composite key whose collective value is not duplicated. I don't see any good candidate columns for an expanded PK among those you described, though. If you drop the PK then it might make sense to add a synthetic, autogenerated PK column.

Also, if I have a row and I update the values of that row, is there any way to keep the original values of the row and insert a clone of that row below, with the updated values and creation/modification date updated automatically?

If you want to ensure that this happens automatically, however a row happens to be updated, then look into triggers. If you want a way to automate it, but you're willing to make the user ask for the behavior, then consider a stored procedure.

SQL Server : Duplicate Primary Key error

A few suggestions (that are not really a full answer, but with the given data, is as far as I can go).

In some occasions, you may have data as text (such as 1.000000001 and 1.000000002) that are different in your text files, but become the same once converted into their destination types, because of truncation, rounding, or other kind of conversion effect. This might be the reason why you cannot find duplicates in your text file, yet the database finds them.

There are few techniques you could use and check:

1. Apart from the imported data ... is there any previous data already on the target table? If so, there you have a possible source for duplicates.

2. Do you have any way to import "row by row" (or in batches) so that this can help you locate the offending one?

If none of the previous alternatives are possible, you can follow this process:

• Create a table with the same structure as your target one, but without any PRIMARY KEY or UNIQUE constraints. Let's call it load_table

• Import your data to this table. It should not complaint of PRIMARY KEY constraints because there isn't any.

• Perform the following query to find out duplicates:

  SELECT 
      k1, k2, k3 ... kn 
  FROM 
      load_table
  GROUP BY
      k1, k2, k3 ... kn
  HAVING
      count(*) > 1
  

  where k1, k2, k3 ... kn are all the columns that would comprise the primary key of your target table.

Using these techniques, you will find the duplicates that SQL Server finds but elude the methods you've used up-until-now.

ignore_dup_key = on in SQL Server does not ignore duplicates in a table

From the documentation for create index:

Arguments

UNIQUE

Creates a unique index on a table or view. A unique index is
one in which no two rows are permitted to have the same index key
value. A clustered index on a view must be unique.

The Database Engine does not allow creating a unique index on columns
that already include duplicate values, whether or not IGNORE_DUP_KEY
is set to ON. If this is tried, the Database Engine displays an error
message.

---

Related Topics