SqlServer uses cursors to update data in batches

Keywords: SQL Server SQL

Intro

Cursors can be very useful sometimes. When updating a small part of data, they can easily update the data. There is no need to write another small tool to do this, just write SQL directly

Sample

Here is a practical example:

-- Declare field variables
DECLARE @RegionCode INT;
DECLARE @RegionName NVARCHAR(64);
DECLARE @ProvinceId INT;

-- declare cursor
DECLARE ProvinceCursor CURSOR FOR(
SELECT Id AS ProvinceId, region.RegionCode,region.RegionName FROM dbo.Provinces AS province
JOIN dbo.Regions AS region ON province.Name=SUBSTRING(region.RegionName,1, LEN(province.Name)) AND region.RegionType=1
);

-- Open cursor
OPEN ProvinceCursor;

-- Move cursor, load data
FETCH NEXT FROM ProvinceCursor
INTO @ProvinceId,@RegionCode,@RegionName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Operate according to the cursor data, only the SQL Script, or direct UPDATE´╝îSee what you need

    PRINT 'UPDATE dbo.Provinces SET Code = ' + CONVERT(NVARCHAR(12), @RegionCode)+', Name = N'''+@RegionName +''' WHERE Id = ' + CONVERT(NVARCHAR(12), @provinceId) +';';
    
        -- Move cursor to next data
        FETCH NEXT FROM ProvinceCursor
    INTO @ProvinceId,@RegionCode,@RegionName;
END;
CLOSE ProvinceCursor;
DEALLOCATE ProvinceCursor;

Another Sample

DECLARE @projectId nvarchar(36)  -- Declare variables
DECLARE My_Cursor CURSOR --Define cursors
FOR (SELECT OriginalProjectId FROM dbo.CommunityProjects
WHERE CommunityId = -1) --Find out the required collection and put it in the cursor
OPEN My_Cursor; --Open cursor
FETCH NEXT FROM My_Cursor INTO @projectId;
WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE dbo.CommunityProjects
        SET CommunityId = CAST(ISNULL((
            SELECT ZhongyiCommunityId FROM dbo.CommunityMappings
            WHERE FangdiCommunityId = @projectId
        ),'-1') AS INT)
        WHERE OriginalProjectId = @projectId

        FETCH NEXT FROM My_Cursor INTO @projectId;
    END
CLOSE My_Cursor; --Close cursor
DEALLOCATE My_Cursor; --Releasing cursors

and more

DECLARE @RegionCode INT;
DECLARE @RegionName NVARCHAR(64);
DECLARE @provinceId INT;
DECLARE ProvinceCursor CURSOR FOR(
SELECT RegionCode,
       RegionName
FROM dbo.Regions
WHERE RegionType = 1);
OPEN ProvinceCursor;
FETCH NEXT FROM ProvinceCursor
INTO @RegionCode,
     @RegionName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @provinceId =ISNULL((SELECT Id FROM dbo.Provinces WHERE Name = @RegionName), 0);
    IF @provinceId > 0
        PRINT 'UPDATE dbo.Provinces SET Code = ' + CONVERT(NVARCHAR(12), @RegionCode)+' WHERE Id = ' + CONVERT(NVARCHAR(12), @provinceId) +';';
    ELSE
        PRINT 'INSERT INTO dbo.Provinces(Name,Code) VALUES(N''' + @RegionName + ''',' + CONVERT(NVARCHAR(12), @RegionCode)+ ');';
    FETCH NEXT FROM ProvinceCursor
    INTO @RegionCode,
         @RegionName;
END;
CLOSE ProvinceCursor;
DEALLOCATE ProvinceCursor;

More

When doing some small data operations, cursors are very convenient, and cursors are flexible. You can only generate SQL to update the data, or print out the values before and after the data update, so as to recover the data after the error data update

Reference

Posted by shock on Tue, 04 Feb 2020 10:02:03 -0800