DEFINITIONS

Definitions More Info.
Definition ID4.053
TitleSQL
CategoryNOTES
Definitiondelete ederken baska tabloya insert etme,arsivleme, archive yapma
Definition DescriptionUSE [DBA]
GO
/****** Object: StoredProcedure [dbo].[ALoginLogDB_Archiver] Script Date: 5.01.2021 11:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
EXEC [DBA].[dbo].[ALoginLogDB_Archiver]
@BlokBoyutu = 100
,@KacGunlukVeriTutulacak = 90
,@BeklemeSuresi = '00:00:01'
,@KacDkCalissin = 1
,@BitisSaati = NULL -- '07:00'
*/

ALTER PROCEDURE [dbo].[ALoginLogDB_Archiver] @BlokBoyutu INT,
@KacGunlukVeriTutulacak INT,
@BeklemeSuresi VARCHAR(8) = '00:00:01',
@KacDkCalissin INT = 5,
@BitisSaati VARCHAR(5) = NULL

WITH RECOMPILE
AS
SET NOCOUNT ON

DECLARE @TasimaSayac BIGINT = 0
DECLARE @rowcount BIGINT

IF (@BitisSaati IS NULL)
BEGIN
SET @BitisSaati = (SELECT
LEFT(RIGHT(CONVERT(VARCHAR(20), DATEADD(MINUTE, @KacDkCalissin, GETDATE()), 113), 8), 5))
END

PRINT '------/ Started: ' + CONVERT(NVARCHAR(19), GETDATE(), 104) + ' ' + CONVERT(NVARCHAR(16), GETDATE(), 108)


WHILE (LEFT(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 113), 8), 5) < @BitisSaati)
BEGIN

DELETE TOP (@BlokBoyutu) FROM [AtlantisDB].[dbo].[ALoginLogDB]
OUTPUT DELETED.[DummyId]
, DELETED.[UserRefNo]
, DELETED.[LoginTime]
, DELETED.[IP]
, DELETED.[Source]
, DELETED.[LastModifyDate]
, DELETED.[CreationDate]
, DELETED.[Version]
, DELETED.[DeviceVersion]
, DELETED.[DeviceId]
, DELETED.[DeviceManufacturer]
, DELETED.[DeviceModel]
, DELETED.[Operator]
INTO [ArchiveDB].[dbo].[ALoginLogDB]
(
[DummyId]
, [UserRefNo]
, [LoginTime]
, [IP]
, [Source]
, [LastModifyDate]
, [CreationDate]
, [Version]
, [DeviceVersion]
, [DeviceId]
, [DeviceManufacturer]
, [DeviceModel]
, [Operator]
)
WHERE [CreationDate] < DATEADD(D, -@KacGunlukVeriTutulacak, DATEADD(DD, DATEDIFF(D, 0, GETDATE()), 0))
OPTION (RECOMPILE, MAXDOP 8)

SET @rowcount = ISNULL(@@rowcount, 0)
SET @TasimaSayac = @TasimaSayac + @rowcount

IF (@rowcount = 0)
BREAK

WAITFOR DELAY @BeklemeSuresi
END


PRINT '------/ Moved ' + CAST(@TasimaSayac AS NVARCHAR(30)) + ' rows.'
PRINT '------/ Finished: ' + CONVERT(NVARCHAR(19), GETDATE(), 104) + ' ' + CONVERT(NVARCHAR(16), GETDATE(), 108)




RecordBycunay
Record Date05-01-2021 11:58:25
Düzenle
Kopyala
Sil