MERGE Conflict when updating data through dataTable as parameter in Stored ProcedureRSS

1 reply

Last post Jul 30, 2019 05:09 AM by Yang Shen

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
inkaln
Member

91 Points

382 Posts

MERGE Conflict when updating data through dataTable as parameter in Stored Procedure

Jul 29, 2019 06:17 PM|inkaln|LINK

"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."

I'm not sure why I'm getting the above error as I have used three columns in the ON clause to make sure records are distinct based on the three columns combined. I removed any duplicated from the DataTable being passed but still getting the above error when trying to execute the storedProcedure. I have the following table type created:

CREATE TYPE [dbo].[ValueType] AS TABLE(
[EntityId] [int] NOT NULL,
[AttributeId] [int] NOT NULL,
[ID] [varchar](256) NOT NULL,
[Value] [varchar](500) NULL,
[CreatedDTS] [datetime] NULL
)

I have the following stored procedure that takes dataTable as parameter

ALTER PROCEDURE [dbo].[UpdateValues]
@Dt ValueType READONLY
AS
BEGIN

SET NOCOUNT ON;

MERGE UserValue AS val
USING (SELECT * FROM @Dt) AS Source
ON val.EntityId= Source.EntityId AND val.AttributeId = Source.AttributeId AND val.ID = Source.ID
WHEN MATCHED THEN
UPDATE SET val.[Value] = Source.[Value]
WHEN NOT MATCHED THEN
INSERT (EntityId, AttributeId,ID, [Value])
VALUES (Source.EntityId, Source.AttributeId, Source.ID, Source.[Value]);
End

In the code, I've placed the following to make sure rows are unique.

cmd.CommandType = CommandType.StoredProcedure;

DataView view = new DataView(tbl);
DataTable distinctValues = view.ToTable(true, "EntityId", "AttributeId", "ID", "Value", "CreatedDTS");

cmd.Parameters.Add(new SqlParameter("@Dt", SqlDbType.Structured));

cmd.Parameters["@Dt"].Value = distinctValues;

cmd.ExecuteNonQuery();

How do I see which records are creating the MERGE conflict? There are over 25,000 records being passed in the dataTable....

Yang Shen

Contributor

2860 Points

910 Posts

Re: MERGE Conflict when updating data through dataTable as parameter in Stored Procedure

Jul 30, 2019 05:09 AM|Yang Shen|LINK

Hi inkaln,

inkaln
"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."

This error occurs when you have duplicates in your table.

It seems there are still duplicates after you set below:

inkaln
ON val.EntityId= Source.EntityId AND val.AttributeId = Source.AttributeId AND val.ID = Source.ID

In your c# code, below part will not actually pick distinct dataview out of the datatable for you, because there are five columns in your dataview which you just want to three of them.

inkaln
DataView view = new DataView(tbl);
DataTable distinctValues = view.ToTable(true, "EntityId", "AttributeId", "ID", "Value", "CreatedDTS");

Please refer to: DataView.ToTable()

Here i made a demo based on the code you provided and description.

In SQL, create two tables and the procedure:

CREATE table sourcetable(
[EntityId] [int] NOT NULL,
[AttributeId] [int] NOT NULL,
[ID] [varchar](256) NOT NULL,
[Value] [varchar](500) NULL,
[CreatedDTS] [datetime] NULL
)

CREATE table targettable(
[EntityId] [int] NOT NULL,
[AttributeId] [int] NOT NULL,
[ID] [varchar](256) NOT NULL,
[Value] [varchar](500) NULL,
[CreatedDTS] [datetime] NULL
)

insert into sourcetable values(1,10,'100','value1','2019-1-10')
insert into sourcetable values(2,20,'200','value2','2019-2-10')
insert into sourcetable values(3,30,'300','value3','2019-3-10')
insert into sourcetable values(4,40,'400','value4','2019-4-10')
insert into sourcetable values(5,50,'500','value5','2019-5-10')
insert into sourcetable values(4,40,'400','value6','2019-6-10')
insert into sourcetable values(5,50,'500','value7','2019-7-10')

---------------------------------------------
CREATE TYPE [dbo].[ValueType] AS TABLE(
[EntityId] [int] NOT NULL,
[AttributeId] [int] NOT NULL,
[ID] [varchar](256) NOT NULL,
[Value] [varchar](500) NULL,
[CreatedDTS] [datetime] NULL
)
---------------------------------------------
create proc [UpdateValues]
@Dt targettable READONLY
as
begin
SET NOCOUNT ON;
MERGE sourcetable AS val
USING (SELECT * FROM @Dt) AS Source
ON val.EntityId= Source.EntityId AND val.AttributeId = Source.AttributeId AND val.ID = Source.ID
WHEN MATCHED THEN 
UPDATE SET val.[Value] = Source.[Value]
WHEN NOT MATCHED THEN 
INSERT (EntityId, AttributeId,ID, [Value])
VALUES (Source.EntityId, Source.AttributeId, Source.ID, Source.[Value]);
End

In C# code:

public string constr = ConfigurationManager.ConnectionStrings["DConString"].ConnectionString;

        protected DataTable GetSourceTable()
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                string sql = "select * from sourcetable";
                SqlDataAdapter sda = new SqlDataAdapter(sql,con);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                DataTable dt = ds.Tables[0];
                return dt;
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(constr);

            SqlCommand cmd = new SqlCommand("UpdateValues",con);

            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            DataTable tbl = GetSourceTable();
            DataView view = new DataView(tbl);
            DataTable distinctValues = view.ToTable(true, "EntityId", "AttributeId", "ID", "Value", "CreatedDTS");

            cmd.Parameters.Add(new SqlParameter("@Dt", SqlDbType.Structured));

            cmd.Parameters["@Dt"].Value = distinctValues;

            cmd.ExecuteNonQuery();

            con.Close();
        }

This will reproduce yourproblem.

My opion is that even you merge on three fields in your procedure, there are still some rows with same "EntityId", "AttributeId" and "ID".

My suggestion is that you can try to filter when you create the tbl. For example,

select * from sourcetable where value in (select min(value) from sourcetable group by EntityId)

Or, please provide complete related code of how you create tbl and excute procedure in c#.

Best Regard,

Yang Shen

‹ Previous Thread|Next Thread ›