更新（如果不存在）在SQL Server中使用XML输入参数插入

问题描述：

CREATE TABLE [dbo].[TelecommunicationsNumber] 
(
    [ID] [int] NOT NULL, 
    [ContactTypeID] [int] NOT NULL, 
    [CountryID] [int] NOT NULL 
)

这是我对上述表格的示例XML输入。更新（如果不存在）在SQL Server中使用XML输入参数插入

DECLARE @TelecommunicationsNumberList XML = '<TelecommunicationsNumber><ContactTypeID>2</ContactTypeID><CountryID>1</CountryID></TelecommunicationsNumber><TelecommunicationsNumber><ContactTypeID>4</ContactTypeID><CountryID>1</CountryID></TelecommunicationsNumber>'

我想出了如下的UPDATE SQL查询。

UPDATE TelecommunicationsNumber 
SET ContactTypeID = n.ContactTypeID, 
    CountryID = n.CountryID 
FROM (SELECT 
      T.C.value('(ContactTypeID)[1]', 'INT') AS ContactTypeID, 
      T.C.value('(CountryID)[1]', 'INT') AS CountryID 
     FROM 
      @TelecommunicationsNumberList.nodes('/TelecommunicationsNumber') AS T (C)) AS n 
WHERE 
    TelecommunicationsNumber.ContactTypeID = n.ContactTypeID

我如何可以插入如果输入XML和TelecommunicationsNumber表做了新的记录，如果存在，存在相同的ContactTypeID和更新。

为了做到这一点，我必须取行以检查天气是否存在或不存在相同的ContactTypeID。

问题：我无法弄清楚SELECT查询。如何通过编写SELECT查询来集成插入和更新查询。

我使用下面的查询来插入记录。

INSERT INTO TelecommunicationsNumber (ContactTypeID,CountryID) 
     SELECT 
      Entries.value('(ContactTypeID)[1]', 'INT') AS 'ContactTypeID', 
      Entries.value('(CountryID)[1]', 'nvarchar(256)') AS 'CountryID' 
     FROM 
      @TelecommunicationsNumberList.nodes('/TelecommunicationsNumber') AS TelecommunicationsNumberEntries (Entries)

看一看['merge'（https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql），它允许你指定一个'更新'当一行存在时，'插入'当它不 – Andomar

@Andomar感谢您的评论。我能够使用'merge'命令解决我的问题。现在它工作正常。 –

答

我设法用MERGE命令解决了这个问题。

; 
    WITH TelecommunicationsNumber 
    AS (SELECT 
    ParamValues.x1.value('ContactTypeID[1]', 'int') AS ContactTypeID, 
    ParamValues.x1.value('CountryID[1]', 'int') AS CountryID 
    FROM @TelecommunicationsNumberList.nodes('/TelecommunicationsNumber') AS ParamValues (x1)) 
    MERGE INTO dbo.TelecommunicationsNumber AS old 
    USING TelecommunicationsNumber AS new 
    ON (new.ContactTypeID = old.ContactTypeID) 
    WHEN MATCHED THEN UPDATE SET 
    old.CountryID = new.CountryID 
    WHEN NOT MATCHED THEN 
    INSERT (ContactTypeID, CountryID) 
    VALUES (new.ContactTypeID, new.CountryID);

更新（如果不存在）在SQL Server中使用XML输入参数插入

相关推荐