DEFINITIONS

Definitions More Info.
Definition ID12.109
TitleSQL
CategoryNOTES
DefinitionUPDATE WITH WHILE LOOP
Definition Description/* buradaki onemle nokta, temp tabloya identitym ID koymaktan geçer */

DECLARE @rowc INT
DECLARE @minTableId INT
DECLARE @maxTableId INT

SET @rowc=0
WHILE( @rowc<=1000)--8600 kere daha dönmeli

BEGIN
BEGIN TRAN;
SELECT @minTableId=MIN(TableId)
FROM tempResumeDolulukOrani_HEDE2
WHERE ISUPDATED=0

SELECT @maxTableId=@minTableId+1000
INSERT INTO tempResumeDolulukOrani_Updated_HEDE7 ([TableId], [RefNumber], [Status], [Name], [Surname], [MStatus], [Sex], [BirthDate], [MilService], [Email], [Education], [EducationS], [AllPositionCodes], [Experience], [DrivingLicence], [PostingDate], [PostingDateN], [Photo_Exists], [insertdate], [Kisilik], [Engel], [EKategori], [EYuzde], [EAciklama], [Employer], [PositionName], [EhliyetSinifi], [EhliyetVerilisTarihi], [Sigara], [ExperienceN], [EhliyetSinifi2], [EhliyetVerilisTarihi2], [TCKimlik], [Executive], [ExGizlilik], [ExIslemTarihi], [LoginId], [CVLanguage], [CVName], [CvEnable], [DefaultCv], [PCity1], [PCity2], [PCity3], [PCity4], [PCountry1], [PCountry2], [PCountry3], [PCountry4], [CCountry], [CCity], [CDistrict], [CPart], [CCountryD], [CCityD], [CDistrictD], [CPartD], [WorkStatus], [IsKurNo], [Title], [SektorKodu], [PozisyonUnvanID], [PozisyonGrupID], [IsAlaniID], [Unvan], [HighSchoolTip], [HighSchoolDept], [Nationality], [DolulukOrani], [TecilTarihi], [WUlkeID], [WSehirID], [NameSurname], [Userid], [CreateDate], [CalismaTercihi], [CreationDate], [LastModifyDate], [MaasBeklentisiGozuksun], [MaasBeklentisiAlt], [MaasBeklentisiUst], [CvUpdateDateN], [PublicCvStatus], [IsPublicCvView], [LastPdfCreationDate], [ExperienceMonth], [JobExperienceLastUpdateDate], [EducationLastUpdateDate])
SELECT TOP 1000 B.TableId,A.[RefNumber], A.[Status], A.[Name], A.[Surname], A.[MStatus], A.[Sex], A.[BirthDate], A.[MilService], A.[Email], A.[Education], A.[EducationS], A.[AllPositionCodes], A.[Experience], A.[DrivingLicence], A.[PostingDate], A.[PostingDateN], A.[Photo_Exists], A.[insertdate], A.[Kisilik], A.[Engel], A.[EKategori], A.[EYuzde], A.[EAciklama], A.[Employer], A.[PositionName], A.[EhliyetSinifi], A.[EhliyetVerilisTarihi], A.[Sigara], A.[ExperienceN], A.[EhliyetSinifi2], A.[EhliyetVerilisTarihi2], A.[TCKimlik], A.[Executive], A.[ExGizlilik], A.[ExIslemTarihi], A.[LoginId], A.[CVLanguage], A.[CVName], A.[CvEnable], A.[DefaultCv], A.[PCity1], A.[PCity2], A.[PCity3], A.[PCity4], A.[PCountry1], A.[PCountry2], A.[PCountry3], A.[PCountry4], A.[CCountry], A.[CCity], A.[CDistrict], A.[CPart], A.[CCountryD], A.[CCityD], A.[CDistrictD], A.[CPartD], A.[WorkStatus], A.[IsKurNo], A.[Title], A.[SektorKodu], A.[PozisyonUnvanID], A.[PozisyonGrupID], A.[IsAlaniID], A.[Unvan], A.[HighSchoolTip], A.[HighSchoolDept], A.[Nationality], A.[DolulukOrani], A.[TecilTarihi], A.[WUlkeID], [WSehirID], A.[NameSurname], A.[Userid], A.[CreateDate], A.[CalismaTercihi], A.[CreationDate], A.[LastModifyDate], A.[MaasBeklentisiGozuksun], A.[MaasBeklentisiAlt], [MaasBeklentisiUst], A.[CvUpdateDateN], A.[PublicCvStatus], A.[IsPublicCvView], A.[LastPdfCreationDate], A.[ExperienceMonth], A.[JobExperienceLastUpdateDate], A.[EducationLastUpdateDate]
FROM ResumeMaster A
JOIN tempResumeDolulukOrani_HEDE2 B ON A.RefNumber=B.ResumeId
AND B.ISUPDATED=0
AND (B.TableId>= @minTableId AND B.TableId<@maxTableId)
ORDER BY B.TableId
OPTION(MAXDOP 10)

UPDATE top (1000) A
SET A.DolulukOrani = CONVERT(VARCHAR,IIF(B.DolulukOrani > 100, 100, B.DolulukOrani)), A.LastModifyDate = GETDATE()
FROM ResumeMaster A
JOIN tempResumeDolulukOrani_HEDE2 B ON A.RefNumber=B.ResumeId
AND B.ISUPDATED=0
AND (B.TableId>= @minTableId AND B.TableId<@maxTableId)
OPTION(MAXDOP 10)

UPDATE top(1000) B
SET B.ISUPDATED=1
FROM tempResumeDolulukOrani_HEDE2 B
WHERE ISUPDATED=0
AND (B.TableId>= @minTableId AND B.TableId<@maxTableId)
OPTION(MAXDOP 10)

SET @minTableId=@maxTableId
COMMIT TRAN;

set @rowc = @rowc+1
WAITFOR DELAY '00:00:00.200' --Two tenths of a seconds
END
RecordBycunay
Record Date01-11-2023 11:23:41
Düzenle
Kopyala
Sil