CODEWITHSUNDEEP
sql-server
VansFannel
VansFannel

Reputation: 45921

Row update if row exists. Insert it if row doesn't exist

I'm developing a SQL SERVER 2012 express and developer solution.

I will receive an xml in an stored procedure. In the stored procedure I will parse the xml and insert its data into a table.

My problem here is that in this xml could contain data that exists on the table, and I need to update the data on the table with the new one.

I don't want to check if each row in xml exists on the table.

I think I can use IGNORE_DUP_KEY but I'm not sure.

How can I update or insert new data without checking it?

This is the table where I want to insert (or update) the new data:

CREATE TABLE [dbo].[CODES]
(
    [ID_CODE] [bigint] IDENTITY(1,1) NOT NULL,
    [CODE_LEVEL] [tinyint] NOT NULL,
    [CODE] [nvarchar](20) NOT NULL,
    [COMMISIONING_FLAG] [tinyint] NOT NULL,
    [IS_TRANSMITTED] [bit] NOT NULL,
    [TIMESPAN] [datetime] NULL,
    [USERNAME] [nvarchar](50) NULL,
    [SOURCE] [nvarchar](50) NULL,
    [REASON] [nvarchar](200) NULL
     CONSTRAINT [PK_CODES] PRIMARY KEY CLUSTERED 
    (
        [CODE_LEVEL] ASC,
        [CODE] ASC
    )
)

Upvotes: 0

Views: 114

Answers (1)

itzik Paz
itzik Paz

Reputation: 433

The "IGNORE_DUP_KEY" parameter ,is ignore inserting new row, if he is already exists, but it is not dealing with update in case it exists.

the solution to your request is by MERGE or DML operation (INSERT/UPDATE/DELETE) .

BTW, The parameter "IGNORE_DUP_KEY" is covering existsnce for the index key only (index column).

Upvotes: 1

Related Questions