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

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)?

CodePudding user response：

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.

CodePudding user response：

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);

CodePudding user response：

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;