Update, if not exists Insert using XML input parameter in SQL Server

Question

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

Here is my sample XML input to the above mentioned table.

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

I figured out the UPDATE SQL query as below.

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

How can I insert a new record if the input XML and the TelecommunicationsNumber table does exists the same ContactTypeID and update if exists.

In order to do that first I have to fetch the rows in order to check weather the same ContactTypeID exists or not.

QUESTION: I am unable to figure out the SELECT query. How can I integrate both the insert and update queries by writing the SELECT query.

I use the below query to INSERT the records.

  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)

Have a look at merge which allows you to specify an update when a row exists, and an insert when it does not — Andomar
– Andomar, Commented Aug 13, 2017 at 17:52
@Andomar Thanks for the comment. I was able to resolve my problem using the merge command. Now its working fine. — Harsha W
– Harsha W, Commented Aug 21, 2017 at 3:48

Harsha W · Accepted Answer · 2017-08-21 03:46:58Z

1

I managed to resolve the issue using MERGE command.

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

answered Aug 21, 2017 at 3:46

Harsha W

3,3867 gold badges54 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update, if not exists Insert using XML input parameter in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related