Método para insert ou delete em lote

[FabianoSouza 5]

Tenho situações em que preciso fazer o insert ou delete de vários registros numa "tacada só", em lote.

 

Minha dúvida é em relação ao melhor método de realizar esse tipo de de operação.

 

1) Loop convencional (é assim que faço hoje)

Algo como

 

While @variavel IS NULL...

executa insert ou delete

Next

Creio que isso não seja o melhor caminho pois precisa executar a mesma consulta várias vezes.

 

2) Criar uma tab temporário, adicionar à tab os dados a serem excluídos ou inseridos e depois executar a consulta usando o conteúdo da tab temporária como valores.

 

 

Se o caminho for a opção 2, gostaria de contar com a ajuda dos colegas para criar um "modelo".

Recebo na stored procedure uma string assim: valor1, valor2, valor3, valor4...

Precisaria gravar estes valores numa tabela temporária e depois rodar o insert/delete

 

Obrigado desde já.

 

 

 

[Motta 645]

Prefiro mais simples o (1).

 

Sendo realmente massivo , milhõe de registros , pode-se pensar em "desligar" contraints , triggers ou a gravação do auditlog.

[FabianoSouza 5]

Motta,

Eu uso loop mas é na aplicação. Agora quero tentar usar direto no banco.

Se puder, pode postar algum código que separa os valores da string e execute a consulta em loop?

 

Obrigado.

[Diemerson 12]

Fala Fabiano, Tranquilão?

 

Depois de quebrar a string em uma tabela, você pode fazer um join com a tabela que quer deletar ou com as demais tabelas que formam os dados que quer inserir... Fiz um exemplo maroto, pra te ajudar, qualquer coisa, só entrar em contato...

 

Abraço!

Diémerson S. Campos

 

 

use tempdb

go

---------------------------------------------

--Criação da tabela para teste

---------------------------------------------

if object_id('tempdb..#deletar') is not null

drop table #deletar

create table #deletar (codigo char(6), descricao varchar(10))

insert into #deletar values ('valor1','r2d2')

insert into #deletar values ('valor2','c3po')

insert into #deletar values ('valor3','yoda')

insert into #deletar values ('valor4','ig88')

insert into #deletar values ('valor5','boba')

---------------------------------------------

---------------------------------------------

go

---------------------------------------------

--criação da procedure

---------------------------------------------

if object_id('tempdb..prc_deleta_lote') is not null

drop procedure prc_deleta_lote

go

create procedure prc_deleta_lote (@string varchar(max),@separador char(1))

as

begin

--criação da tabela temporaria que vai receber a string

if object_id('tempdb..#temp') is not null

drop table #temp

create table #temp (valor varchar(500))

--quebra da string em uma tabela temporária

declare @valor varchar(500)

while charindex(@separador,@string)<> 0

begin

set @valor = left(@string,charindex(@separador,@string) -1)

set @string = substring(@string,charindex(@separador,@string)+1,len(@string))

if len(@valor) > 0

insert into #temp select rtrim(ltrim(@valor))

end

if len(@string) > 0

insert into #temp select rtrim(ltrim(@string))

--deleção dos registros

delete del

from #temp tmp

join #deletar del

on tmp.valor = del.codigo

end

---------------------------------------------

---------------------------------------------

go

---------------------------------------------

--Execução do teste

---------------------------------------------

select * from #deletar

exec prc_deleta_lote 'valor1, valor2, valor3, valor4', ','

select * from #deletar

[Diemerson 12]

Tinha uma pequena treta na sintaxe, passou batido qdo colei ai, mas já está corrigido! :D .

 

De qualquer forma, se quiser fazer um loop, vc pode aproveitar o loop do while, após o valor ser definido na variavel @valor (lembrando que o ultimo valor é inserido fora do while) ou fazer um cursor na tabela #temp...

 

Abraço.

Diémerson S. Campos

[FabianoSouza 5]

Olá Diemerson!

Vou testar. Vamos ver se consigo resolver essa bucha antes de acabar o ano kkk

 

Valew.

[FabianoSouza 5]

Olá Diemerson!

Cara, muito obrigado pela ajuda.

 

Comecei fazendo testes com o código que postou. Adaptei e a coisa funcionou.

Fiquei empolgado e quis avançar um pouco mais. Achei que seria uma boa ideia

criar uma function pois vou usar esta funcionalidade várias vezes no sistema.

 

Depois de muito garimpar encontrei isso.

https://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html

 

Funcionou perfect!

 

A function quebra a string e retorna uma tabela com os valores.

Depois só precisei rodar um delete usando como parâmetro os valores da tabela criada.

 

Ah, não manjo nada de SQL. Portanto, se tiver considerações a fazer, fique à vontade (serão de grande valia).

 

Outra coisa, notei que dessa forma (usando tabela temp/function) o desempenho é melhor do a opção 1 que citei lá no início. Procede?

 

A function Split é essa:

USE [meuBanco]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 31/12/2015 15:14:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

O procedimento é esse.

@cd int,
@cip int,
@stringItens nvarchar(4000)

AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

DECLARE @linhas int -- indicador de sucesso da operação
BEGIN
DELETE 
minhaTab 
FROM 
minhaTab AS tab 
INNER JOIN  
dbo.Split(@stringItens, ',') ON tab.|Id = Data 

SET @linhas = @@ROWCOUNT 

IF(@@error = 1)
BEGIN
ROLLBACK TRANSACTION
PRINT 'Ocorreu um erro.' 
RETURN
END
ELSE
SELECT @linhas AS linhas
COMMIT TRANSACTION
END

[FabianoSouza 5]

Salve, salve Diemerson!

 

Meu amigo, uma última ajuda neste post.

Estou tentando usar seu código para fazer insert (a questão do delete está resolvida).

Como você falou no tópico #5, estou tentando aproveitar o loop, colocando o insert lá.

 

O problema é que grava mas sempre faltando um registro.

Se envio 5 itens para o banco, o código grava apenas 4.

 

Só falta isso para ficar redondo :)

 

[Diemerson 12]

Bom dia, Fabiano! Tranquilo?

 

Como te falei, é inserido fora do while, então vc faz o insert dentro do loop utilizando a variável @valor e logo após o "end" vc repete o insert utilizando a variável @string, o ultimo valor vai ficar nela. assim como faço o insert da tabela #temp. Você pode fazer um insert com select, não? como funciona seu insert?

 

Abraço.

[onomedeladaniela 0]

Meio atrasada,rs....mas tenho um método bem simples:

 

Declare 
 @Rowcount int = 1,
 @Dateini datetime, 
 @Datefim datetime, 
 @cont int = 0

set @Dateini = '20180101'
set @Datefim = '20180131'

 

while @Rowcount > 0
Begin
  delete top (10000) from tablex where data between @Dateini and @Datefim
  set @Rowcount = @@ROWCOUNT

  print '****************************'+convert(varchar,@cont)+ 'º lote deletado...****************************'
  print ''
 set @cont = @cont + 1
end