DEFINITIONS

Definitions More Info.
Definition ID2.011
TitleSQL
CategoryNOTES
DefinitionBlok olarak silme DELETE
Definition Description--SELECT * INTO [dbo].[IsAramaLogDB_YEDEK] FROM [dbo].[IsAramaLogDB]

-- KONTROL SCRIPT COUNT
--SELECT COUNT(*)
--FROM [dbo].[ALoginDB_DeleteForIsAramaLogDB]WITH(NOLOCK)
--WHERE IsDeleted=0
--OPTION(MAXDOP 10)


-- Geçici Tablo varsa kaldırılır
USE KNetLogs
GO
IF OBJECT_ID('dbo.ALoginDB_DeleteForIsAramaLogDB', 'U') IS NOT NULL
DROP TABLE [KNetLogs].[dbo].[ALoginDB_DeleteForIsAramaLogDB];
GO

-- Silim için geçici tablo oluşturulur
CREATE TABLE ALoginDB_DeleteForIsAramaLogDB (
ID INT IDENTITY NOT NULL,
IsAramaLogID BIGINT PRIMARY KEY ,
UserRefNo INT,
IsDeleted BIT DEFAULT 0,
ProcessDate DATETIME DEFAULT getdate()
)
GO

INSERT INTO [KNetLogs].[dbo].[ALoginDB_DeleteForIsAramaLogDB](IsAramaLogID,UserRefNo)
SELECT Id,LoginID FROM (SELECT ROW_NUMBER () OVER( PARTITION BY i.loginId ORDER BY Id DESC ) as rownumber,Id,LoginID
FROM [KNetLogs].[dbo].[IsAramaLogDB] i WITH(NOLOCK)
INNER JOIN Atlantisdb.dbo.alogindb a (nolock) ON i.LoginID=a.userrefno
AND a.UserRefNo IS NOT NULL
AND a.hesapsilindi=0
) sb
WHERE sb.rownumber>=10
OPTION(MAXDOP 10)
GO
USE [KNetLogs]
GO
CREATE NONCLUSTERED INDEX [IX_IsInserted_UserRefNo_CT] ON [dbo].[ALoginDB_DeleteForIsAramaLogDB]
(
[IsDeleted] ASC,
[UserRefNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,MAXDOP=10, FILLFACTOR = 90)

GO

--Blok olarak data silme döngsüne bırakılır.
DECLARE @UserRefNo INT

DECLARE DeleteIsAramaLogDB CURSOR FOR
SELECT UserRefNo
FROM [KNetLogs].[dbo].[ALoginDB_DeleteForIsAramaLogDB] a (nolock)
WHERE IsDeleted=0
OPEN DeleteIsAramaLogDB;
FETCH NEXT FROM DeleteIsAramaLogDB INTO @UserRefNo
WHILE @@FETCH_STATUS=0
BEGIN
DELETE
FROM [KNetLogs].[dbo].[IsAramaLogDB]
WHERE Id IN (SELECT sb.Id
FROM (SELECT ROW_NUMBER () OVER( PARTITION BY i.loginId ORDER BY i.Id DESC ) as rownumber,LoginID,i.Id
FROM [KNetLogs].[dbo].[IsAramaLogDB] i WITH(NOLOCK)
WHERE LoginID=@UserRefNo) sb
WHERE sb.rownumber>10)
OPTION(MAXDOP 10)

UPDATE [KNetLogs].[dbo].[ALoginDB_DeleteForIsAramaLogDB]
SET IsDeleted=1
WHERE IsDeleted=0
AND UserRefNo= @UserRefNo
FETCH NEXT FROM DeleteIsAramaLogDB INTO @UserRefNo
END
CLOSE DeleteIsAramaLogDB;
DEALLOCATE DeleteIsAramaLogDB;
RecordBycunay
Record Date22-03-2020 14:40:19
Düzenle
Kopyala
Sil