enrique_aeo
asked on
sql server 2005 - eliminate blocks of 500 by 500.
Please experts,
I need this requirement:
- Delete records leaving records of the last 60 days.
- The range of debugging for the deletion of records is 3 months. Within these three months should eliminate blocks of 500 by 500.
Since this table contains a lot of records, it is necessary to clean the table for periods, considering the date
I am using sql server 2005, i can not use cursor.
You need to clear the transaction records 500 records without locking the entire table completely and releasing locks on each transaction
I need this requirement:
- Delete records leaving records of the last 60 days.
- The range of debugging for the deletion of records is 3 months. Within these three months should eliminate blocks of 500 by 500.
Since this table contains a lot of records, it is necessary to clean the table for periods, considering the date
I am using sql server 2005, i can not use cursor.
You need to clear the transaction records 500 records without locking the entire table completely and releasing locks on each transaction
ASKER
THE table is very large, that is why you want to delete in groups of three months and within those three months of 500 in 500
this previously answered question (PAQ) may be of assistance - it discusse using batches.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please,
Anyway I need your script support
The range of debugging for the deletion of records is 3 months. Within these three months should eliminate blocks of 500 by 500.
Anyway I need your script support
The range of debugging for the deletion of records is 3 months. Within these three months should eliminate blocks of 500 by 500.
Please show the table structure,
use WITH (ROWLOCK) for delete statement.
And using temp table add all ids that going to delete from the table and then delete it from table one by one using loop.
use WITH (ROWLOCK) for delete statement.
And using temp table add all ids that going to delete from the table and then delete it from table one by one using loop.
ASKER
This is the table
--Create table and its columns
CREATE TABLE [dbo].[pruebas_rln] (
[id] [int] NOT NULL IDENTITY (1, 1),
[sesion] [varchar](34) NULL,
[pais] [varchar](2) NULL,
[tienda] [varchar](10) NULL,
[kiosco] [varchar](10) NULL,
[tipoConsulta] [varchar](1) NULL,
[tipoProducto] [varchar](20) NULL,
[tipoTarjeta] [varchar](20) NULL,
[numTarjeta] [varchar](20) NULL,
[tipoDocumento] [varchar](1) NULL,
[numDocumento] [varchar](20) NULL,
[codigoPantalla] [varchar](20) NULL,
[tiempoVisita] [int] NULL,
[tiempoRespuesta] [int] NULL,
[impresionRealizada] [varchar](1) NULL,
[fechaRegistro] [datetime] NULL);
--Create table and its columns
CREATE TABLE [dbo].[pruebas_rln] (
[id] [int] NOT NULL IDENTITY (1, 1),
[sesion] [varchar](34) NULL,
[pais] [varchar](2) NULL,
[tienda] [varchar](10) NULL,
[kiosco] [varchar](10) NULL,
[tipoConsulta] [varchar](1) NULL,
[tipoProducto] [varchar](20) NULL,
[tipoTarjeta] [varchar](20) NULL,
[numTarjeta] [varchar](20) NULL,
[tipoDocumento] [varchar](1) NULL,
[numDocumento] [varchar](20) NULL,
[codigoPantalla] [varchar](20) NULL,
[tiempoVisita] [int] NULL,
[tiempoRespuesta] [int] NULL,
[impresionRealizada] [varchar](1) NULL,
[fechaRegistro] [datetime] NULL);
ASKER
I attached some data
script-depuracion---Copy.txt
script-depuracion---Copy.txt
ASKER
Please experts, you support is very important for me
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi experts
I test but too long
declare @contador int = 0
declare @DeleteDate as datetime
select @DeleteDate = cast( convert(varchar(10),datead d(day, -60, getdate()),101) as datetime) -- this gets you 60 days ago at midnight
--print @DeleteDate
while (select count(1) from [dbo].[pruebas_rln] where fechaRegistro < @DeleteDate) >0
begin
delete top (50)
from [dbo].[pruebas_rln]
where fechaRegistro > @DeleteDate;
--@contador = @contador +1;
print @contador
end
Hello experts, I tried it but the query takes more than five minutes, I'm not sure but I think it has entered an infinite loop, please support
I test but too long
declare @contador int = 0
declare @DeleteDate as datetime
select @DeleteDate = cast( convert(varchar(10),datead
--print @DeleteDate
while (select count(1) from [dbo].[pruebas_rln] where fechaRegistro < @DeleteDate) >0
begin
delete top (50)
from [dbo].[pruebas_rln]
where fechaRegistro > @DeleteDate;
--@contador = @contador +1;
print @contador
end
Hello experts, I tried it but the query takes more than five minutes, I'm not sure but I think it has entered an infinite loop, please support
How many record are there if you
declare @DeleteDate as datetime
select @DeleteDate = cast( convert(varchar(10),dateadd(day, -60, getdate()),101) as datetime) -- this gets you 60 days ago at midnight
--print @DeleteDate
select count(1) from [dbo].[pruebas_rln] where fechaRegistro < @DeleteDate
ASKER
for my test I have 304 rows
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To delete the oldest 500 records older than 60 days (identified by DateTime column myTable.recordDateTime) in a table called myTable:
Open in new window