How to Insert/Update or Skip if identical in a single query in SQL Server?

Question

I have a dataset of around 100000 rows. I have been using the following query to insert into a SQL Server Database table where column1 is the Primary Key,

INSERT INTO dbo.table1(column1, column2, column3)
SELECT 'value1', 'value2', 'value1'
EXCEPT
SELECT column1, column2, column3 FROM dbo.table1

It is working fine. Now i need to check if the row exists by the primary key value before inserting. If row exists and all the other column values are identical then skip the row else update all the column values with respect to the primary key value and if not exists insert new row. How can I achieve this in a SQL query (Not Stored Procedure)?

This answered post may help you in that direction >> https://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update. — lemon, Oct 13 '22 at 11:35
I am unable to figure out the skipping part. Using Merge on Primary Keys to Insert and Update. But how do I skip if all column values are identical? If rows are identical it need not to be updated every time. — mapuna, Oct 13 '22 at 12:02
Generally you don't bother adding that complexity to it... you just update regardless, because the result isn't any different. Do you expect there to be a lot of rows in this case? If you really want to exclude updates you add another filter `WHERE SRC.col1<>TGT.col1 OR SRC.col2 <> tgt.col2.....` — Nick.Mc, Oct 13 '22 at 13:00

score 1 · Answer 1 · answered Oct 13 '22 at 13:01

you have to add condition on the WHEN MATCHED AND [...]
Like this:

MERGE dbo.table1 AS TARGET
USING (
    SELECT 'value1' AS column1,'value2' AS column2,'value3' AS column3
) AS SOURCE
    ON TARGET.column1 = SOURCE.column1
WHEN MATCHED AND (TARGET.column2 != Source.column2 OR TARGET.column3 != source.column3)
    THEN
        UPDATE
        SET TARGET.column2 = Source.column2
            ,TARGET.column3 = source.column3
WHEN NOT MATCHED
    THEN
        INSERT (column1,column2,column3)
        VALUES (SOURCE.column1,SOURCE.column2,SOURCE.column3);

Currently I am using this exact query except the 'OR' condition in MATCHED. — mapuna, Oct 13 '22 at 13:14

Charlieface · Accepted Answer · 2022-10-13T13:07:04.293

You need a MERGE in order to do both inserts and updates. You can use a VALUES virtual table as the source, or another table or table variable.

MERGE dbo.table1 AS t WITH (HOLDLOCK)
USING (VALUES
    ('SomePK', 'value1', 'value2', 'value1')
) s(PK, column1, column2, column3)
ON s.PK = t.PK      -- do not put any other conditions here
WHEN NOT MATCHED THEN
    INSERT (column1, column2, column3)
    VALUES (s.column1, s.column2, s.column3)
WHEN MATCHED AND EXISTS (
    SELECT s.column1, s.column2, s.column3
    EXCEPT
    SELECT t.column1, t.column2, t.column3
  ) THEN UPDATE SET
    column1 = s.column1,
    column2 = s.column2,
    column3 = s.column3
;

The EXCEPT syntax is only necessary if you have nullable columns. Otherwise you can just do normal <> conditions.

You can also just use an INSERT UPDATE combination, along with a transaction and the correct locking hints

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

INSERT INTO dbo.table1 (PK, column1, column2, column3)
SELECT 'SomePK', 'value1', 'value2', 'value1'
WHERE NOT EXISTS (SELECT 1
    FROM dbo.table1 t1 WITH (HOLDLOCK, UPDLOCK)
    WHERE t1.PK = 'SomePK'
);

IF @@ROWCOUNT = 0
    UPDATE dbo.table1 WITH (HOLDLOCK, UPDLOCK)
    SET column1 = 'value1',
        column2 = 'value2',
        column3 = 'value1'
    WHERE PK = 'SomePK'
      AND EXISTS (
        SELECT 'value1', 'value2', 'value1'
        EXCEPT
        SELECT t.column1, t.column2, t.column3
      );

COMMIT;

You can swap around the INSERT and UPDATE if you think the latter is more likely than the former.

See also this article for further information.

The first one should do the trick. This is a life saver. Thank you — mapuna, Oct 13 '22 at 13:17

score 0 · Answer 3 · answered Oct 13 '22 at 13:02

Please try the following conceptual example.

The trick is working via the following clause:

WHERE NOT EXISTS (SELECT S.* INTERSECT SELECT T.*)

If you are using MERGE statement, you just need to add that clause to the UPDATE part:

WHEN MATCHED AND NOT EXISTS (SELECT S.* INTERSECT SELECT T.*)

SQL

-- DDL and sample data population, start
DECLARE @Source TABLE (APK INT IDENTITY PRIMARY KEY, ID_NUMBER INT, UpdatedOn DATETIMEOFFSET(3));
DECLARE @Target TABLE (APK INT IDENTITY PRIMARY KEY, ID_NUMBER INT, UpdatedOn DATETIMEOFFSET(3));

INSERT INTO @Source (ID_NUMBER)
  VALUES (null), (null), (7), (7), (5);

INSERT INTO @Target (ID_NUMBER)
  VALUES (null), (7), (null), (7), (4);
-- DDL and sample data population, end

SELECT * FROM @Source;
SELECT * FROM @Target;

UPDATE T 
SET T.ID_NUMBER = S.ID_NUMBER
   , T.UpdatedOn = SYSDATETIMEOFFSET()
FROM @Target AS T
    INNER JOIN @Source AS S
      ON T.APK = S.APK
WHERE NOT EXISTS (SELECT S.* INTERSECT SELECT T.*);

-- test
SELECT * FROM @Target;

How to Insert/Update or Skip if identical in a single query in SQL Server?

3 Answers3