CODEWITHSUNDEEP
sql-servert-sql
Álvaro García
Álvaro García

Reputation: 19396

How is the sintaxis to use ignore_dup_key?

I am trying to ignore duplicates when I insert rows in a many to many relationship table, but all the examples that I find is modifying the key in the table.

I would like to know if there is some way to set this only for the inserts that I want to do, something like that:

insert into Table(IDA, IDB) VALUES(1,2) ignore_dup_key = ON;

Thanks.

Upvotes: 0

Views: 61

Answers (2)

Eduardo Silva
Eduardo Silva

Reputation: 625

I've just edited this answer after @Alvaro Garcia Comment

insert into Table(IDA, IDB) 
SELECT T.IDA, T.IDB 
FROM (SELECT 1 as IDA, 2 as IDB) T
LEFT JOIN Table T2 ON T.IDA = T2.IDA AND T.IDB = T2.IDB
WHERE T2.IDA is null

before I was just checking existence and then insert.

Upvotes: 1

Jason A. Long
Jason A. Long

Reputation: 4442

"ignore_dup_key = ON" is a property that you define when creating an index. It is not a query hint.

CREATE UNIQUE NONCLUSTERED INDEX ix_table 
    ON dbo.Table (KeyColumn)
    WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY];

Upvotes: 1

Related Questions