Sql-Server

幫助合併聲明

  • September 10, 2018

我想比較表中的Distinct snfrom HMI_Tempto -> 如果存在，我想更新值 - 得到這個語法檢查！- 如果不存在，我想將數據插入我已經嘗試過這種語法，但我收到以下錯誤：sn``HMI``sn``sn``HMI_Temp``HMI

無效的列名 ‘cb’

無效的列名 ’lp'

無效的列名 cn

無效的列名 stn

這是我的語法 - 我應該如何重寫它來實現我想要的結果？

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

這是我使用的更新語句，但如果不存在，現在將扳手放入其中以進行插入:(

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

為了避免語法錯誤，您需要在USING子句中包含所有插入到目標表中的列。所以一個語法上有效的合併語句將是：

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

由於您的預期功能包括更新現有行的能力，因此您還需要一個WHEN MATCHED子句，將目標表中的所需列設置為源表中的那些值。就像是：

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

然而，正如 Aaron 在他的回答中提到的那樣，MERGE它充滿了其他潛在的問題。

我使用了以下最低限度完整、可驗證的範例：

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

數據如下：

SELECT *
FROM dbo.HMI;

╔═════╦══════╦══════╦══════╦═════════╦═════════╗
║ sID ║ stn ║ sn ║ cn ║ lp ║ cb ║
╠═════╬══════╬══════╬══════╬═════════╬═════════╣
║ 1 ║ 8888 ║ 測試 ║ 測試 ║ 測試123 ║ 測試456 ║
║ 2 ║ 8887 ║ A ║ B ║ C ║ D ║
╚═════╩══════╩══════╩══════╩═════════╩═════════╝

SELECT *
FROM dbo.HMI_Temp;

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

合併聲明：

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

結果，發布MERGE：

SELECT *
FROM dbo.HMI;

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

如果dbo.HMI_Temp表包含非唯一行，如：

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

執行MERGE語句時，您會看到以下錯誤：

消息 8672，級別 16，狀態 1，第 67 行

MERGE 語句多次嘗試更新或刪除同一行。當目標行匹配多個源行時會發生這種情況。MERGE 語句不能多次 UPDATE/DELETE 目標表的同一行。優化 ON 子句以確保目標行最多匹配一個源行，或使用 GROUP BY 子句對源行進行分組。

由於您現在已經澄清您還需要將cn列用作唯一性，您也許可以使用此合併語句：

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

注意，我添加AND Target.cn = Source.cn到WHERE子句中。

引用自：https://dba.stackexchange.com/questions/212003

相關問答

Sql-Server

在合併中使用 Where 子句

  • June 9, 2020
檢視問答
Sql-Server

在合併更新語句中使用聚合

  • May 7, 2020
檢視問答
Sql-Server

合併兩個表

  • October 5, 2016
檢視問答
Sql-Server

合併同一張表中的兩條記錄，保持外鍵關係不變

  • April 3, 2015
檢視問答
Sql-Server

執行 DELETE 儲存過程，但發現 WHILE 循環中的性能隨著迭代次數的增加而下降

  • March 23, 2022
檢視問答
Sql-Server

對象“SPROC”、數據庫“DATABASE”、模式“dbo”的 EXECUTE 權限被拒絕

  • March 10, 2022
檢視問答