2

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
  • Well, just looking at your statement SOURCE is a list of distinct sn's. My guess is that SQL Server just does not know anything about the other columns that are giving you errors because they are not in Source. – Scott Hodgin Jul 12 '18 at 11:30
  • @ScottHodgin - I only want to compare Distinct sn values, but need to insert the other fields. Is there a differentw ay to write the statement to achieve the same result? – user2676140 Jul 12 '18 at 11:31
4

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.

  • Each stn can exist multiple times in the table, however the cn would be different for each instance of the stn. I need to update/insert the data checking both the cn and the stn – user2676140 Jul 12 '18 at 14:17
  • I added a new MERGE example at the bottom of my answer. – Max Vernon Jul 12 '18 at 14:21
5

Here is at least a part of the problem: If you have two rows in HMI_Temp with the same value for sn but different values for cb (or any other column), which one should SQL Server pick?

Here is a better pattern than MERGE, IMHO, which has all kinds of issues:

INSERT dbo.HMI(stn,sn,cn,lp,cb)
SELECT stn,sn,cn,lp,cb
FROM
(
  SELECT stn,sn,cn,lp,cb,rn = ROW_NUMBER() OVER 
    (
      PARTITION BY sn,cn -- makes sn + cn distinct
      ORDER BY sID       -- picks which row to keep
    )
  FROM dbo.HMI_Temp
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM dbo.HMI WHERE sn = hmi.sn
  )
) AS x
WHERE rn = 1;

This is taking the row with the lowest sID but you may wish to rank the rows a different way or use MIN/MAX on each of the other four columns to determine which row you want to keep. Hard to tell from your question.

  • See my edit. Apology for not providing clear question. The stn can exist multiple times in the table, however each instance of the stn will have a unique value for the cn. Which leads to the update needing to only update and insert values where the sn and the cn are the same. – user2676140 Jul 12 '18 at 14:12
  • I edit my OP to show my original update statement if that helps at all – user2676140 Jul 12 '18 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.