Sql-server – Help With Merge Statement

mergesql serversql-server-2008-r2t-sql

I am wanting to compare the Distinct sn from HMI_Temp to sn in my table HMI -> if the sn exists, I want to update the values – got this syntax CHECK! – if the sn does NOT exist, I want to insert the data from HMI_Temp into HMI I have tried this syntax, but I get errors of:

Invalid column name 'cb'
Invalid column name 'lp'

Invalid column name cn

Invalid column name stn

And this is my syntax – how should I re-write this to accomplish my desired result?

MERGE INTO HMI AS Target
USING (SELECT DISTINCT sn FROM HMI_Temp) AS Source ON Target.sn = Source.sn
WHEN NOT MATCHED THEN
INSERT (lp, cb, cn, sn, stn) VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

DDL:

CREATE TABLE [dbo].[HMI_Temp]
(
    [sID] [int] IDENTITY(1,1) NOT NULL,
    stn [float] NULL,
    [sn] [nvarchar](255) NULL,
    [cn] [nvarchar](max) NULL,
    [lp] [nvarchar](max) NULL,
    [cb] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [sID] ASC
    ) WITH 
      (
           PAD_INDEX = OFF
         , STATISTICS_NORECOMPUTE = OFF
         , IGNORE_DUP_KEY = OFF
         , ALLOW_ROW_LOCKS = ON
         , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
) ON [PRIMARY] 
TEXTIMAGE_ON [PRIMARY]  
GO

SET IDENTITY_INSERT [dbo].[HMI_Temp] ON 
GO

INSERT [dbo].[HMI_Temp] ([sID], stn, [sn], [cn], [lp], [cb]) 
VALUES (1, 8888, N'Test', N'Test', N'Test123', N'Test456')
VALUES (1, 8888, N'SecondTest', N'SecondTest', N'SecondTest123', N'SecondTest456')
GO

This is the update statement I was using that worked, but now throwing a wrench in it for the insert if not exists 🙁

UPDATE y
SET y.lp = x.lp
,y.cb = x.cb
,y.cn = x.cn
FROM HMI y
INNER JOIN HMI_Temp x
ON y.stn = x.stn
AND y.sn = x.sn
AND y.cn = x.cn

Best Answer

In order to avoid the syntax error, you need to include all columns in the USING clause that are being inserted into the target table. So a syntactically valid merge statement would be:

MERGE INTO HMI AS Target
USING (SELECT DISTINCT sn, lp, cb, cn, stn FROM HMI_Temp) AS Source ON Target.sn = Source.sn
WHEN NOT MATCHED THEN
INSERT (lp, cb, cn, sn, stn) VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

Since your intended functionality includes the ability to update existing rows, you also need a WHEN MATCHED clause that sets the desired columns in the target table to those values from the source table. Something like:

MERGE INTO HMI AS Target
USING (
    SELECT DISTINCT sn
        , lp
        , cb
        , cn
        , stn 
    FROM HMI_Temp
    ) AS Source ON Target.sn = Source.sn
WHEN MATCHED THEN
UPDATE SET Target.lp = Source.lp
    , Target.cb = Source.cb
    , Target.cn = Source.cn
    , Target.stn = Source.stn
WHEN NOT MATCHED THEN
INSERT (lp, cb, cn, sn, stn) 
VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

However, as mentioned by Aaron in his answer, MERGE is fraught with other potential issues.

I used the following minimally complete, verifiable example:

USE tempdb;

DROP TABLE IF EXISTS dbo.HMI;
CREATE TABLE dbo.HMI
(
    sID int IDENTITY(1,1) NOT NULL
    , stn float NULL
    , sn nvarchar(255) NULL
    , cn nvarchar(max) NULL
    , lp nvarchar(max) NULL
    , cb nvarchar(max) NULL
    , PRIMARY KEY CLUSTERED 
      (
          sID ASC
      ) WITH 
        (
            PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
) ON [PRIMARY] 
TEXTIMAGE_ON [PRIMARY];
GO

INSERT dbo.HMI (stn, sn, cn, lp, cb) 
VALUES (8888, N'Test', N'Test', N'Test123', N'Test456')
    , (8887, N'A', N'B', N'C', N'D');


DROP TABLE IF EXISTS dbo.HMI_Temp;
CREATE TABLE dbo.HMI_Temp
(
    sID int IDENTITY(1,1) NOT NULL
    , stn float NULL
    , sn nvarchar(255) NULL
    , cn nvarchar(max) NULL
    , lp nvarchar(max) NULL
    , cb nvarchar(max) NULL
    , PRIMARY KEY CLUSTERED 
      (
          sID ASC
      ) WITH 
        (
            PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
) ON [PRIMARY] 
TEXTIMAGE_ON [PRIMARY];
GO

INSERT dbo.HMI_Temp (stn, sn, cn, lp, cb) 
VALUES (8888, N'Test', N'F', N'G', N'H')

The data looks like:

SELECT *
FROM dbo.HMI;

╔═════╦══════╦══════╦══════╦═════════╦═════════╗
║ sID ║ stn  ║  sn  ║  cn  ║   lp    ║   cb    ║
╠═════╬══════╬══════╬══════╬═════════╬═════════╣
║   1 ║ 8888 ║ Test ║ Test ║ Test123 ║ Test456 ║
║   2 ║ 8887 ║ A    ║ B    ║ C       ║ D       ║
╚═════╩══════╩══════╩══════╩═════════╩═════════╝
SELECT *
FROM dbo.HMI_Temp;

╔═════╦══════╦══════╦════╦════╦════╗
║ sID ║ stn  ║  sn  ║ cn ║ lp ║ cb ║
╠═════╬══════╬══════╬════╬════╬════╣
║   1 ║ 8888 ║ Test ║ F  ║ G  ║ H  ║
╚═════╩══════╩══════╩════╩════╩════╝

The merge statement:

MERGE INTO dbo.HMI AS Target
USING (
    SELECT DISTINCT sn
        , lp
        , cb
        , cn
        , stn 
    FROM dbo.HMI_Temp
    ) AS Source ON Target.sn = Source.sn
WHEN MATCHED THEN
    UPDATE SET Target.lp = Source.lp
        , Target.cb = Source.cb
        , Target.cn = Source.cn
        , Target.stn = Source.stn
WHEN NOT MATCHED THEN
    INSERT (lp, cb, cn, sn, stn) 
    VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

The results, post MERGE:

SELECT *
FROM dbo.HMI;

╔═════╦══════╦══════╦════╦════╦════╗
║ sID ║ stn  ║  sn  ║ cn ║ lp ║ cb ║
╠═════╬══════╬══════╬════╬════╬════╣
║   1 ║ 8888 ║ Test ║ F  ║ G  ║ H  ║
║   2 ║ 8887 ║ A    ║ B  ║ C  ║ D  ║
╚═════╩══════╩══════╩════╩════╩════╝

If the dbo.HMI_Temp table contains non-unique rows, as in:

INSERT dbo.HMI_Temp (stn, sn, cn, lp, cb) 
VALUES (8888, N'Test', N'F', N'G', N'H')
    , (8888, N'Test', N'I', N'J', N'K')
    , (8888, N'Test', N'L', N'M', N'N');

When you run the MERGE statement, you'll see this error:

Msg 8672, Level 16, State 1, Line 67
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Since you've now clarified that you also need to use the cn column as a uniquifier, you might be able to use this merge statement:

MERGE INTO dbo.HMI AS Target
USING (
    SELECT sn
        , lp
        , cb
        , cn
        , stn 
    FROM dbo.HMI_Temp
    ) AS Source ON Target.sn = Source.sn
        AND Target.cn = Source.cn
WHEN MATCHED THEN
    UPDATE SET Target.lp = Source.lp
        , Target.cb = Source.cb
        , Target.cn = Source.cn
        , Target.stn = Source.stn
WHEN NOT MATCHED THEN
    INSERT (lp, cb, cn, sn, stn) 
    VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

Notice, I added AND Target.cn = Source.cn to the WHERE clause.

Related Question