Need help with SP. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with SP.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-11-21 : 18:05:31

Hi,

How can I run the archive process below as a batch, I want to insert like 10,000 rows and delete 10,000 rows until the condition is meet.
My sp below is stop after 10,000 because is a Break but some tables have over 1 millions rows and the others had less than 10,000.

How can I run as a batch such as 10,000 at a time. Please feel free to change the sp below.

Thank you so much in advance.

IF OBJECT_ID('dbo.usp_ArchiveHIXOps', 'p') IS NOT NULL
DROP PROCedure dbo.usp_ArchiveHIXOps
GO

CREATE PROCedure dbo.usp_ArchiveHIXOps
AS
/***********************************************************************************************
**
** Requirements: Archive data older than 90 days.
**
**
** Return code: 0 -- Success.
** 1 -- Failed.
**
** Written by:
**
** Written date:
**
** Modifications:
** ----------------------------------
** Date: Author: Reasons:
** ------------+-----------------------------------------------------------------------
**
**
***********************************************************************************************/
SET nocount ON;
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
,@ErrorNumber INT = ERROR_NUMBER()
,@ErrorState INT = ERROR_STATE()
,@CutOffDate DATE = CONVERT(CHAR(8), DATEADD(mm, -3, CURRENT_TIMESTAMP), 112)

--SELECT @CutOffDate

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

BEGIN TRY

--WHILE ( 1 = 1)
--BEGIN

INSERT ArchiveHIXOps.dbo.PortalActivityLog
SELECT TOP 10000
[Id],
[WebServer],
[RequestTime],
[ClientId],
[LogCorrelationId],
[URL],
[HttpMethod],
[URLReferrer],
[UserAgent],
[UserHostName],
[UserHostAddress],
[Culture],
[LoggedInUserId],
[AccountId],
[HttpStatus],
[PageTitle],
[MvcAction],
[MvcController],
[MvcControllerType],
[RequestCanceled],
[ExceptionMessage],
[ExceptionStackTrace],
[ActionResultType],
[PageFlowId],
[PageFlowStepId],
[PageFlowAction],
[PageFlowStepTitle],
[PageFlowFurthestStep],
[ModelValidationErrors],
[Overflow]
FROM dbo.PortalActivityLog
WHERE (CONVERT(CHAR(8), [RequestTime], 112) < @CutOffDate );

DELETE TOP (1000)
FROM dbo.PortalActivityLog
WHERE (CONVERT(CHAR(8), [RequestTime], 112) < @CutOffDate );

---------

-- This table has over 1 millions rows need to archive and delete.

INSERT ArchiveHIXOps.dbo.TraceLog ( [Id], [Message], [CreatedDate] )
SELECT TOP (10000)
[Id],
[Message],
[CreatedDate]
FROM dbo.TraceLog
WHERE ( CONVERT(CHAR(8), CreatedDate, 112) < @CutOffDate );

DELETE TOP (10000)
FROM dbo.TraceLog
WHERE ( CONVERT(CHAR(8), CreatedDate, 112) < @CutOffDate );

---------

INSERT ArchiveHIXOps.dbo.MelissaStatistics
SELECT TOP (10000)
[HitCounter],
[UpdatedDate],
[CreatedDate]
FROM dbo.MelissaStatistics
WHERE ( CONVERT(CHAR(8), CreatedDate, 112) < @CutOffDate );

DELETE TOP (10000)
FROM dbo.MelissaStatistics
WHERE( CONVERT(CHAR(8), CreatedDate, 112) < @CutOffDate );

-----------

--IF ( @@ROWCOUNT < 10000 )
-- BREAK
--END --end after the WHILE Loop.

END TRY

BEGIN CATCH

THROW @ErrorMessage, @ErrorNumber, @ErrorState

END CATCH
GO

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-21 : 19:25:15

hey, maybe u can try this method?


declare @a table(col1 int)
insert into @a select 1 union all select 2 union all select 3
declare @b table(col1 int)

delete top (2) from @a
output deleted.col1 into @b

select * from @a
select * from @b

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 05:46:11

use paging logic like below and use it in delete and insert statements

http://www.codeproject.com/Articles/12338/Using-ROW_NUMBER-to-paginate-your-data-with-SQL-Se

so all statements will have WHERE condition based on RowNumber as

RN BETWEEn @Start AND @End

and add a looping logic on top to increment @start and @end by 1000
ie start 1ith 1 and 1000 then make them 1001 -2000,2001-300,... until it reaches your table maxcount

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-11-22 : 11:21:23

Hi visakhm,

I look at the link you provided but it doesn't help what I try to accomplish. I try to archive data older than 3 Months and I want to do in a batches 10,000 at a time. Any suggestions would greatly appreciate. I know you're the man for SQL Guru.

Thank you.

quote:
Originally posted by visakh16

use paging logic like below and use it in delete and insert statements

http://www.codeproject.com/Articles/12338/Using-ROW_NUMBER-to-paginate-your-data-with-SQL-Se

so all statements will have WHERE condition based on RowNumber as

RN BETWEEn @Start AND @End

and add a looping logic on top to increment @start and @end by 1000
ie start 1ith 1 and 1000 then make them 1001 -2000,2001-300,... until it reaches your table maxcount

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-23 : 03:46:13

quote:
Originally posted by NguyenL71

Hi visakhm,

I look at the link you provided but it doesn't help what I try to accomplish. I try to archive data older than 3 Months and I want to do in a batches 10,000 at a time. Any suggestions would greatly appreciate. I know you're the man for SQL Guru.

Thank you.

quote:
Originally posted by visakh16

use paging logic like below and use it in delete and insert statements

http://www.codeproject.com/Articles/12338/Using-ROW_NUMBER-to-paginate-your-data-with-SQL-Se

so all statements will have WHERE condition based on RowNumber as

RN BETWEEn @Start AND @End

and add a looping logic on top to increment @start and @end by 1000
ie start 1ith 1 and 1000 then make them 1001 -2000,2001-300,... until it reaches your table maxcount

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

why whats the issue?
DO youve a primary key on your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-25 : 04:37:57

my method would not work? lol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources