August 11, 2015 at 7:56 am
Ed Wagner (8/11/2015)
It sounds like this is a maintenance process that should be run off-hours.You have calls in your table from the year 1899? :ermm:
Did you try to update the code to do what you want it to do? What do you have now?
The records have bogus dates.
Unfortunately I get your code to work.
Thank you for your script.
I reverted back to a simple script that is not really what I need.
DECLARE @ArchiveDate DateTime
SET @ArchiveDate = '1999-01-01' -- Pass into a paramter
--SELECT @ArchiveDate AS ArchiveDate
SET ROWCOUNT 100
BEGIN
BEGIN TRANSACTION
INSERT INTO PrismDataArchive.dbo.tblCallArchive
SELECT *
FROM [PrismDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate
DELETE
FROM [PrismDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate
END
--ROLLBACK TRAN
I get the following data from the source table tblCall when I execute the following:
USE PrismDataSource
GO
SELECT DATEPART(yyyy,Call_Date) AS Call_Date, COUNT(*) AS RecordCount
FROM tblCall WITH (NOLOCK)
GROUP BY DATEPART(yyyy,Call_Date)
ORDER BY Call_Date
Call_DateRecordCount
NULL46
189924
19981095
19997967
20006600
200150483
200267137
200370068
200467311
200568502
200668595
200784438
2008104377
2009151171
2010200165
2011232679
2012242464
2013285255
2014355890
2015236886
[/code]
There are no records are deleted from tblCall.
When I execute the following I get the following results:
USE PrismDataArchive
GO
SELECT DATEPART(yyyy,Call_Date) AS Call_Date, COUNT(*) AS RecordCount
FROM tblCallArchive WITH (NOLOCK)
GROUP BY DATEPART(yyyy,Call_Date)http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
ORDER BY Call_Date
Call_DateRecordCount
1998 200
This should be straight forward. I have done this before on multiply occasions and I did not have this problem.
All that I want to do is copy the records to the archive table and delete from the source table.
I also need to control the number of records that are committed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 11, 2015 at 8:28 am
Does anyone know of an Archive Delete Utility that I could pay for?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 11, 2015 at 8:53 am
I striped the script down and it works.
It lacks Transaction, try, catch etc.
It also lacks the WHILE Loop and the commit size.
DECLARE @ArchiveDate DateTime
SET @ArchiveDate = '2002-01-01' -- Pass into a paramter
BEGIN
BEGIN TRANSACTION
INSERT INTO PrismDataArchive.dbo.tblCallArchive
SELECT *
FROM [PrismDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate
DELETE
FROM [PrismDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate
END
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 11, 2015 at 9:06 am
Welsh Corgi (8/11/2015)
I striped the script down and it works.It lacks Transaction, try, catch etc.
It also lacks the WHILE Loop and the commit size.
DECLARE @ArchiveDate DateTime
SET @ArchiveDate = '2002-01-01' -- Pass into a paramter
BEGIN
BEGIN TRANSACTION
INSERT INTO PrismDataArchive.dbo.tblCallArchive
SELECT *
FROM [PrismDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate
DELETE
FROM [PrismDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate
END
It lacks Transactions? Why do I see a "BEGIN TRANSACTION", and without a COMMIT?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 9:10 am
Welsh Corgi (8/11/2015)
Unfortunately I get your code to work.
On behalf of everyone who is wondering...
Why is it unfortunate that you got someone else's code to work? Usually it's a good thing to get someone else's code to fix a problem.
August 11, 2015 at 9:23 am
Personally, I'd start with something like this and modify it as needed:
declare @BatchSize int = 5000, -- change as desired
@ArchiveDate DateTime = '2002-01-01';
-- If passed in as a parameter
-- set @ArchiveDate = @pArchiveDate; -- Assumes parameter name is @pArchiveDate
while @BatchSize > 0
begin
begin try
begin transaction;
delete top (@BatchSize) from tc
output DELETED.* into PrismDataArchive.dbo.tblCallArchive
from [PrismDataSource].[dbo].[tblCall] tc
where tc.Call_Date < @ArchiveDate;
set @BatchSize = @@rowcount; -- capture number of rows affect
commit transaction;
end try
begin catch
rollback transaction;
-- other error handling code goes here as needed
end catch
end
And then again, it also depends on what I am doing and how much of the data is being deleted versus how much is being kept. There are other options depending on the situation and requirements.
edit: had to fix a few cut/paste errors.
August 11, 2015 at 9:31 am
Lynn Pettis (8/11/2015)
Personally, I'd start with something like this and modify it as needed:
declare @BatchSize int = 5000, -- change as desired
@ArchiveDate DateTime = '2002-01-01';
-- If passed in as a parameter
-- set @ArchiveDate = @pArchiveDate; -- Assumes parameter name is @pArchiveDate
while @BatchSize > 0
begin
begin try
begin transaction;
delete top (@BatchSize) from tc
output DELETED.* into PrismDataArchive.dbo.tblCallArchive
from [PrismDataSource].[dbo].[tblCall] tc
where tc.Call_Date < @ArchiveDate;
set @BatchSize = @@rowcount; -- capture number of rows affect
commit transaction;
end try
begin catch
rollback transaction;
-- other error handling code goes here as needed
end catch
end
And then again, it also depends on what I am doing and how much of the data is being deleted versus how much is being kept. There are other options depending on the situation and requirements.
edit: had to fix a few cut/paste errors.
Looks like a reasonable option to me.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 10:52 am
Lynn Pettis (8/11/2015)
Personally, I'd start with something like this and modify it as needed:
declare @BatchSize int = 5000, -- change as desired
@ArchiveDate DateTime = '2002-01-01';
-- If passed in as a parameter
-- set @ArchiveDate = @pArchiveDate; -- Assumes parameter name is @pArchiveDate
while @BatchSize > 0
begin
begin try
begin transaction;
delete top (@BatchSize) from tc
output DELETED.* into PrismDataArchive.dbo.tblCallArchive
from [PrismDataSource].[dbo].[tblCall] tc
where tc.Call_Date < @ArchiveDate;
set @BatchSize = @@rowcount; -- capture number of rows affect
commit transaction;
end try
begin catch
rollback transaction;
-- other error handling code goes here as needed
end catch
end
And then again, it also depends on what I am doing and how much of the data is being deleted versus how much is being kept. There are other options depending on the situation and requirements.
edit: had to fix a few cut/paste errors.
Thanks Lynn.:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 12, 2015 at 10:01 am
Brandie Tarvin (8/11/2015)
Welsh Corgi (8/11/2015)
Unfortunately I get your code to work.On behalf of everyone who is wondering...
Why is it unfortunate that you got someone else's code to work? Usually it's a good thing to get someone else's code to fix a problem.
That was a typo.
I could not get that code to work.
I did get a response and the code worked out of the box.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 12, 2015 at 10:06 am
Welsh Corgi (8/12/2015)
Brandie Tarvin (8/11/2015)
Welsh Corgi (8/11/2015)
Unfortunately I get your code to work.On behalf of everyone who is wondering...
Why is it unfortunate that you got someone else's code to work? Usually it's a good thing to get someone else's code to fix a problem.
That was a typo.
I could not get that code to work.
Ah. Okay. We hoped that's all it was. @=)
August 12, 2015 at 10:07 am
Alvin Ramard (8/11/2015)
Welsh Corgi (8/11/2015)
I striped the script down and it works.It lacks Transaction, try, catch etc.
It also lacks the WHILE Loop and the commit size.
DECLARE @ArchiveDate DateTime
SET @ArchiveDate = '2002-01-01' -- Pass into a paramter
BEGIN
BEGIN TRANSACTION
INSERT INTO PrismDataArchive.dbo.tblCallArchive
SELECT *
FROM [PrismDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate
DELETE
FROM [PrismDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate
END
It lacks Transactions? Why do I see a "BEGIN TRANSACTION", and without a COMMIT?
I took the COMMIT out so that I could roll it back and it did exactly what I wanted.
Thanks for your help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 12, 2015 at 10:08 am
Scott Coleman (8/10/2015)
One criticism would be using "WHERE DATEPART(yyyy,Call_Date)" everywhere. It is much better to set @ArchiveDate to the oldest instant you want to keep (say midnight Jan 1 of the year after the year to be archived), and use "WHERE Call_Date < @ArchiveDate".If you can use the OUTPUT clause with DELETE, the delete & archive can be one statement and no transaction is needed. This requires that tblCall is not a view, and tblCallArchive has no triggers, check constraints, rules, or foreign keys.
DECLARE @Batchsize INT;DECLARE @ArchiveDate DATETIME;
SELECT @Batchsize INT = 5000; -- Used to preset @@ROWCOUNT
WHILE @@ROWCOUNT > 0 AND (SELECT Active_Indicator FROM ArchiveDriver) = 1 BEGIN
DELETE TOP (@Batchsize)
FROM [PrisimDataSource].[dbo].[tblCall]
OUTPUT DELETED.* INTO [PrimDataArchive].[dbo].[tblCallArchive]
WHERE Call_Date < @ArchiveDate;
END
I omitted the try-catch block because there is no transaction to rollback. The calling program might put a try-catch around the EXEC for this procedure, if there is some error handling you want to do there.
If OUTPUT is not an option and separate INSERT and DELETE statements are required, you need a transaction. But your original code does not guarantee the INSERT and DELETE are handling the same rows (no ORDER BY). A more robust solution would be to fill a table variable with the clustered key values for the next batch of rows, then use them for both statements. This is written as though the table has a single-column integer primary key, named PK. If it is a compound key, the "WHERE PK IN" clauses could be replaced by an INNER JOIN or even WHERE EXISTS. The CATCH block is very basic, I would normally put more transaction state checking around the COMMIT and ROLLBACK but that was not the point of this post.
DECLARE @keys TABLE (K1 INT NOT NULL PRIMARY KEY CLUSTERED);WHILE 1=1 BEGIN
INSERT INTO @keys ( K1 )
SELECT TOP (@Batchsize) PK
FROM [PrisimDataSource].[dbo].[tblCall]
WHERE Call_Date < @ArchiveDate;
IF @@ROWCOUNT = 0 OR (SELECT Active_Indicator FROM ArchiveDriver) = 0 BREAK;
BEGIN TRY
BEGIN TRAN
INSERT INTO [PrimDataArchive].[dbo].[tblCallArchive]
SELECT *
FROM [PrisimDataSource].[dbo].[tblCall]
WHERE PK IN (SELECT PK FROM @keys);
DELETE FROM [PrisimDataSource].[dbo].[tblCall]
WHERE PK IN (SELECT PK FROM @keys);
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
RAISERROR()
RETURN -1
END CATCH
DELETE @keys;
END
I need the Active Flag so that I can stop the script from running.
Currently the script has been running for 35 minutes trying to delete 151,171 records.
Thank you Scott for everything.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 12, 2015 at 10:15 am
Brandie Tarvin (8/12/2015)
Welsh Corgi (8/12/2015)
Brandie Tarvin (8/11/2015)
Welsh Corgi (8/11/2015)
Unfortunately I get your code to work.On behalf of everyone who is wondering...
Why is it unfortunate that you got someone else's code to work? Usually it's a good thing to get someone else's code to fix a problem.
That was a typo.
I could not get that code to work.
Ah. Okay. We hoped that's all it was. @=)
Who is we?:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 12, 2015 at 11:00 am
Welsh Corgi (8/12/2015)
Brandie Tarvin (8/12/2015)
Welsh Corgi (8/12/2015)
Brandie Tarvin (8/11/2015)
Welsh Corgi (8/11/2015)
Unfortunately I get your code to work.On behalf of everyone who is wondering...
Why is it unfortunate that you got someone else's code to work? Usually it's a good thing to get someone else's code to fix a problem.
That was a typo.
I could not get that code to work.
Ah. Okay. We hoped that's all it was. @=)
Who is we?:-)
Those of us following this thread.
August 12, 2015 at 12:23 pm
Welsh Corgi (8/12/2015)
Brandie Tarvin (8/12/2015)
Welsh Corgi (8/12/2015)
Brandie Tarvin (8/11/2015)
Welsh Corgi (8/11/2015)
Unfortunately I get your code to work.On behalf of everyone who is wondering...
Why is it unfortunate that you got someone else's code to work? Usually it's a good thing to get someone else's code to fix a problem.
That was a typo.
I could not get that code to work.
Ah. Okay. We hoped that's all it was. @=)
Who is we?:-)
The collective:alien:
😎
Resistance is futile, you will be assimilated.....
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply