I have a problem inserting a datatable in a sql table (Merge)

2

My problem is that I want to empty a datatable in a temporary folder of sql, my code already arrives at the moment of executing my stored procedure that executes the insertion of the datatable.

Insertion Code in c #

    try
    {
        comando = new SqlCommand("sp_Update_RectificacionesExportaciones", conexion);
        comando.CommandType = CommandType.StoredProcedure;
        comando.Parameters.AddWithValue("@tblRectificacionesExportaciones", dt_rectificacionesExportaciones);
        comando.CommandTimeout = 7200000;

        conexion.Open();
        comando.ExecuteReader();
        return "1";
    }
    catch (SqlException ex)
    {
        return "0";
    }

Code of the sp where the merge is made:

  MERGE INTO texportacion c1
  USING @tblRectificacionesExportaciones c2
  ON c1.idPedimento = c2.idPedimento
  and c1.idProducto = c2.idProducto
  and c1.factura = c2.factura
  and c1.secuencia= c2.secuencia
  and c1.descargado = 0
  WHEN MATCHED THEN
  UPDATE SET 
    c1.valorComercialUSD=c2.valorComercialUSD,
    c1.valorComercialMNX=c2.valorComercialMNX,
    c1.valorAduanaMNX=c2.valorAduanaMNX,
    c1.observación=c2.observación,
    c1.dta=c2.dta,
    c1.prv=c2.prv,
    c1.cove=c2.cove,
    c1.fechaFactura=c2.fechaFactura,
    c1.razonSocialCliente=c2.razonSocialCliente,
    c1.icoterm=c2.icoterm,
    c1.idMoneda=c2.idMoneda,
    c1.factorMonedaExtranjera=c2.factorMonedaExtranjera,
    c1.secuencia=c2.secuencia,
    c1.idFraccion=c2.idFraccion,
    c1.idPaisDestino=c2.idPaisDestino,
    c1.idPaisComprador=c2.idPaisComprador,
    c1.idUnidadComercial=c2.idUnidadComercial,
    c1.cantidad=c2.cantidad,
    c1.precioUnitario=c2.precioUnitario,
    c1.idTasa=c2.idTasa,
    c1.preferencia=c2.preferencia,
    c1.fechaActualizacion=c2.fechaActualizacion;

The error I get is the following:

  

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 can not 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.

Eh validated that each field matches that of the table and that the types of values are similar. I've also checked that the required fields are filled out.

This is the information of my datatable or @tblRectificaciones:

The structure of my table is as follows:

At the moment of doing my insertion if you do it, only the update is the one that you do not realize.

    
asked by David 22.03.2017 в 17:46
source

1 answer

1

The error appears because at least 2 rows in dt_rectificationsExports meet the same conditions for a single record in the text table.

You must fine-tune the conditions for the merge or verify that the data you send to the store procedure does not contain duplicate records.

In case you are not sending duplicate records you must refine this condition:

ON c1.idPedimento = c2.idPedimento
and c1.idProducto = c2.idProducto
and c1.factura = c2.factura
and c1.secuencia= c2.secuencia
and c1.descargado = 0
    
answered by 22.03.2017 / 18:26
source