CODEWITHSUNDEEP
sqlsql-server
Dawid Ohia
Dawid Ohia

Reputation: 16445

INSERT IGNORE in MS SQL Server 2005?

Is there mysql's INSERT IGNORE equivalent in MS SQL Server 2005 or something similar?

Upvotes: 3

Views: 6935

Answers (4)

Stephan
Stephan

Reputation: 6018

I would say you have two viable options:

One is to filter out the wrong values before you try. Maybe add a where clause like:

WHERE newValues NOT IN (SELECT pk_col FROM yourTable)

OR you can use try-catch because whenever you work with errors, normally something with try-catch is your best friend. Try this out:

BEGIN TRY
    INSERT INTO yourTable(pk_col,other_date)
    SELECT 'PK Value That Already Exists','saflaksdfj'
END TRY

BEGIN CATCH
    --You can choose to do anything in response to an error like nothing!
END CATCH

Upvotes: 0

Philipp Andre
Philipp Andre

Reputation: 1047

I don't know what your application is - maybe the REPLACE could help?!

Upvotes: -2

Chris Shaffer
Chris Shaffer

Reputation: 32575

I'm not familiar with the mysql INSERT IGNORE feature, but the documentation I am reading makes it sound like all it does is ignore(silently fail) attempts to insert a record with a primary key that already exists in the table.

If that is what you are looking for, SQL Server has a similar functionality, but you have to apply it to the primary key or unique constraint when you create it.

Read about the IGNORE_DUP_KEY option of a unique index.

Upvotes: 3

davek
davek

Reputation: 22915

you can implement something like this with an INSTEAD OF trigger, intercepting the default insert action to perform updates where existing rows are detected. Not quite as out-of-the-box as the MySql variation, but more flexible.

Upvotes: 2

Related Questions