INSERT IGNORE Statements in SQL Server

We have INSERT statements in MySQL that are based on several circumstances. To prevent duplicates, we can recommend that the insert statement fail if the values are already present. Alternately, we may use the REPLACE command to replace the previously inserted values with the newly added ones. Additionally, MySQL will update the old row with the new values if the ON DUPLICATE KEY UPDATE option is specified in the INSERT statement.

Here are some examples to above specified scenarios.

Replacing duplicate records
MySQL> REPLACE INTO Emp(EmpNo, Ename, Sal) VALUES (2187, ‘Scott Williams’, 2700);
Updating duplicate records
MySQL> INSERT INTO Emp( EmpNo, Ename, Sal) VALUES (2188, ‘Raymond’, 2000)
ON DUPLICATE KEY UPDATE EmpNo = 2188, postTitle = ‘Raymond’, postPublished = 2000;
Ignoring duplicate record
MySQL> INSERT IGNORE INTO Emp(EmpNo, Ename, Sal) VALUES (2199, ‘Paul’, 2000);

We’ll see the SQL Server equivalent to these statements in this article.

If records are discovered in the table, update the values. Insert it if no records are found. You can use MERGE in this situation, but the method following is preferable.

Below is a sample table:
CREATE TABLE dbo.EmpRec(EmpID INT IDENTITY(1001,1), EName VARCHAR(20));
INSERT INTO dbo.EmpRec(EName) VALUES (‘Robert Smith’),(‘Paula Atkinson’), (‘Robert Williams’)

SELECT * FROM dbo.EmpRec
Result:
EmpID || EName
1001 || Robert Smith
1002 || Paula Atkinson
1003 || Robert Williams

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
aaaaUPDATE dbo.EmpRec SET Ename=’John Smith’ WHERE EmpID = 1001;
aaaaIF @@ROWCOUNT = 0
aaaaaaBEGIN
aaaaaaaaINSERT dbo.EmpRec(EName) SELECT ‘Robert Smith’;
aaaaaaEND
COMMIT TRANSACTION;

SELECT * FROM dbo.EmpRec
Result:
EmpID || EName
1001 || John Smith
1002 || Paula Atkinson
1003 || Robert Williams

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
aaaaUPDATE dbo.EmpRec SET Ename=’Will Smith’ WHERE EmpID = 1004;
aaaaIF @@ROWCOUNT = 0
aaaaaaBEGIN
aaaaaaaaINSERT dbo.EmpRec(EName) SELECT ‘Will Smith’;
aaaaaaEND
COMMIT TRANSACTION;

SELECT * FROM dbo.EmpRec
Result:
EmpID || EName
1001 || John Smith
1002 || Paula Atkinson
1003 || Robert Williams
1004 || Will Smith

You can still use the MERGE as indicated below if you think it’s too much of a workaround.
MERGE dbo.EmpRec AS TARGET
USING (VALUES (‘Anatoly’))
AS SOURCE (EName)
ON TARGET.EmpID = 1006
WHEN MATCHED THEN
UPDATE SET EName = SOURCE.EName
WHEN NOT MATCHED THEN
INSERT(EName) VALUES(SOURCE.EName);

Please note that you must use a semi-colon at the end of the statement.

SELECT * FROM dbo.EmpRec
Result:
EmpID || EName
1001 || John Smith
1002 || Paula Atkinson
1003 || Robert Williams
1004 || Will Smith
1005 || Anatoly