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

Hope you find this article helpful.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s