1
I have the following scenario; I need to make a INSERT or UPDATE from a list of List<PRD_ARTIGO_VARIANTE_VALOR>, my doubt is whether there’s any way I can get past the list of a single time by getting the Ids of UPDATE or of INSERT accomplished.
The condition used to UPDATE is not via Id as in the code below.
The way this one works perfectly, but I was wondering if there’s a way to get everything in one go to the database, without having to use the foreach (var variantesValor in variantesValores), like when we make a INSERT of a list with the DAPPER.
public void MergeArtigoVarianteValor(List<PRD_ARTIGO_VARIANTE_VALOR> variantesValores, SqlConnection connection)
{
using (var connection = new SqlConnection(ObterConnectionString()))
{
foreach (var variantesValor in variantesValores)
{
string query = $@" DECLARE @VarianteValor TABLE( ID_PRD_VARIANTE_VALOR int)
MERGE lx_prd.PRD_ARTIGO_VARIANTE_VALOR AS artVar
USING (SELECT @ID_LINX, @ID_ARTIGO, @ID_ATRIBUTO, @ID_ATRIBUTO_DEFINICAO, @LABEL)
AS source ( ID_LINX, ID_ARTIGO, ID_ATRIBUTO, ID_ATRIBUTO_DEFINICAO, LABEL)
ON (artVar.ID_LINX = source.ID_LINX
and artVar.ID_ARTIGO = source.ID_ARTIGO
and artVar.ID_ATRIBUTO = source.ID_ATRIBUTO
and artVar.ID_ATRIBUTO_DEFINICAO = source.ID_ATRIBUTO_DEFINICAO)
WHEN MATCHED THEN
UPDATE SET LABEL = source.LABEL
WHEN NOT MATCHED THEN
INSERT ( ID_LINX, ID_ARTIGO, ID_ATRIBUTO, ID_ATRIBUTO_DEFINICAO, LABEL)
VALUES (source.ID_LINX, source.ID_ARTIGO, source.ID_ATRIBUTO, source.ID_ATRIBUTO_DEFINICAO, source.LABEL)
OUTPUT inserted.ID_PRD_VARIANTE_VALOR INTO @VarianteValor;
SELECT ID_PRD_VARIANTE_VALOR FROM @VarianteValor";
variantesValor.ID_PRD_VARIANTE_VALOR = connection.Query<int>(query, variantesValor, null, true, 3600).First();
}
}
}
I don’t remember ever using it, but SQL specifies the
<output_clause>. https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15– tvdias
@tvdias, yes , but you know how to pass the list in Dapper and recover all Ids in output ?
– Marco Souza
have tried using Bulk Insert/update ?
– Marcos Brinner
@Marcosbrinner, DAPPER has this in the free version ? like doing Insert if it does not exist or update if it exists ?
– Marco Souza
You have the table’s DER?
– Renato Junior
@Marconciliosouza, seriously you put sensitive information of your BD here? just a security tip, you can mask the fields...
– Adjair Costa
@Adjaircosta, even you know where this server knows which server bank will already have a headache. Then I’d still have to have access for you to do something.
– Marco Souza