How to ignore duplicate Primary Key in SQL?

ignore dup key sql server
ignore_dup_key insert
sql merge ignore duplicates
sql server create unique index ignore existing duplicates
sql server on conflict do nothing
ef core ignore_dup_key
allow_row_locks
mysql update on duplicate key ignore

I have an excel sheet with several values which I imported into SQL (book1$) and I want to transfer the values into ProcessList. Several rows have the same primary keys which is the ProcessID because the rows contain original and modified values, both of which I want to keep. How do I make SQL ignore the duplicate primary keys?

I tried the IGNORE_DUP_KEY = ON but for rows with duplicated primary key, only 1 the latest row shows up.

CREATE TABLE dbo.ProcessList
(
    Edited              varchar(1),
    ProcessId           int             NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON),
    Name                varchar(30)     NOT NULL,
    Amount              smallmoney      NOT NULL,
    CreationDate        datetime        NOT NULL,
    ModificationDate    datetime
)

INSERT INTO ProcessList SELECT Edited, ProcessId, Name, Amount, CreationDate, ModificationDate FROM Book1$
SELECT * FROM ProcessList

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?

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.

Can I set ignore_dup_key on for a primary key?, How do I modify the primary key to ignore duplicate key entries ? Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/ Since the key will not allow duplicate values the procedure will fail. One option would be to put a where clause on the insert proc, but the main data table has 10 million+ rows, and this could take a long time.

You drop the constraint. Something like this:

alter table dbo.ProcessList drop constraint PK_ProcessId;

You need to know the constraint name.

In other words, you can't ignore a primary key. It is defined as unique and not-null. If you want the table to have duplicates, then that is not the primary key.

Help, I need to add IGNORE_DUP_KEY = ON to an existing table , Ideally (I think) I just want to ignore duplicate key errors - in the most efficient way possible. (Thus I don't want to have to check the main table for the IDs as part  Several rows have the same primary keys which is the ProcessID because the rows contain original and modified values, both of which I want to keep. How do I make SQL ignore the duplicate primary keys? I tried the IGNORE_DUP_KEY = ON but for rows with duplicated primary key, only 1 the latest row shows up.

try this

INSERT IGNORE INTO ProcessList SELECT Edited, ProcessId, Name, Amount, CreationDate, ModificationDate FROM Book1$ SELECT * FROM ProcessList

how can I ignore duplicate inserts?, Violation of PRIMARY KEY constraint 'PK__ignore_d__3BD0198E9F9BACEA'. Cannot insert duplicate key in object 'dbo.ignore_dup_key'. However, if you are looking to write a query to find duplicates of these groups of columns before applying a primary key to the table that consists of these columns, then this is what you'd want: select t.user_id, t.round, t.tournament_id from table as t group by t.user_id, t.round, t.tournament_id having count(*) > 1

How to get the ignored row while IGNORE_DUP_KEY is ON on SQL , This tutorial shows you how to use the MySQL INSERT IGNORE statement to Note that the IGNORE clause is an extension of MySQL to the SQL standard. CREATE TABLE subscribers ( id INT PRIMARY KEY AUTO_INCREMENT, The UNIQUE constraint ensures that no duplicate email exists in the email column. Suppose you have a table with a single record, where the first field (with value "ID100") is the primary key. ('ID100', 'b', 'c', 'd', 256) Now suppose you want to insert the tuple ('ID100', 'e'

MySQL INSERT IGNORE Statement Explained By Examples, You can use a PRIMARY KEY or a UNIQUE Index on a table with the If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently​  SQL Query to COUNT all rows with duplicate values broken down by primary key. AutoID (primary key and foreign key to violations table driverID) clientID

MySQL - Handling Duplicates, For example, without IGNORE , a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the  The primary key should be set on ID in table 2 The columns and column properties should be the same of course between both tables. This will work the first time you run the below script. The duplicate ID in table 1, will not insert

Comments
  • I'd recommend loading the data into a scratch table with no constraints. Then do further processing to load the data into a table with a primary key and other constraints. Handle the duplicates as part of this process.
  • How many times can a process be edited? As a varchar(1) I'm guessing possibly just once? In which case perhaps your primary key is actually the combination of Edited and ProcessId. E.g. PRIMARY KEY (Edited, ProcessId) rather than defining the primary key on the single column.
  • Creating a composite PK is a brilliant idea to solve this issue if you can't drop your PK (if used as a FK constraint for ex.). Thx @John!