Code mẫu cursor MSSQL Server

Cursor để chép data từ 1 bảng sang bảng khác

DECLARE @name NVARCHAR(MAX), @number INT = 0, @id INT;
DECLARE complex_cursor CURSOR FOR
SELECT Id, Name
FROM dbo.Temp_Province
ORDER BY Id;
OPEN complex_cursor

FETCH NEXT FROM complex_cursor
INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO dbo.StateProvince
( CountryId ,
Name ,
Abbreviation ,
Published ,
DisplayOrder,
IdTmp
)
VALUES ( 230 , -- CountryId - int
@name, -- Name - nvarchar(100)
N'' , -- Abbreviation - nvarchar(100)
1 , -- Published - bit
@number, -- DisplayOrder - int
@id
)
SET @number = @number +1;
-- Get the next vendor.
FETCH NEXT FROM complex_cursor
INTO @id, @name
END
CLOSE complex_cursor;
DEALLOCATE complex_cursor;

Cursor insert dữ liệu với dữ liệu lấy từ nhiều bảng khác nhau

DECLARE @districtname NVARCHAR(MAX), @numberorder INT = 0, @districtid INT, @provinceid INT, @stateprovinceid INT;
DECLARE complex_cursor CURSOR FOR
SELECT DistrictId, Name, ProvinceId
FROM dbo.Temp_District
ORDER BY DistrictId;
OPEN complex_cursor
FETCH NEXT FROM complex_cursor
INTO @districtid, @districtname, @provinceid
WHILE @@FETCH_STATUS = 0
BEGIN
--Get ProvinceId
SET @stateprovinceid = (SELECT Id FROM dbo.StateProvince WHERE IdTmp = @provinceid)

INSERT INTO dbo.District
( StateProvinceId ,
Name ,
Published ,
DisplayOrder
)
VALUES ( @stateprovinceid , -- StateProvinceId - int
@districtname, -- Name - nvarchar(100)
1 , -- Published - bit
@numberorder -- DisplayOrder - int
)

SET @numberorder = @numberorder +1;
-- Get the next vendor.
FETCH NEXT FROM complex_cursor
INTO @districtid, @districtname, @provinceid
END
CLOSE complex_cursor;
DEALLOCATE complex_cursor;

Cursor with IF Condition

DECLARE @Id BIGINT, @finalParentId BIGINT, @curId NVARCHAR(MAX), @curParentId NVARCHAR(MAX), @curName NVARCHAR(MAX)
DECLARE complex_cursor CURSOR FOR
SELECT Id, CurId, CurParentId, Title
FROM dbo.SupplyCategories
OPEN complex_cursor
FETCH NEXT FROM complex_cursor
INTO @Id, @curId, @curParentId, @curName
WHILE @@FETCH_STATUS = 0
BEGIN

IF @curParentId != 0
BEGIN
--Get ProvinceId

SET @finalParentId = (SELECT Id FROM dbo.SupplyCategories WHERE CurId = @curParentId);
PRINT(CONVERT(NVARCHAR(MAX), @Id) + ' CurName: ' + @curName + ' CurId: ' + @curId + ' CurParentId: ' + @curParentId + ' FinalParentId: ' + CONVERT(NVARCHAR(MAX), @finalParentId));
UPDATE SupplyCategories SET ParentId = @finalParentId WHERE Id = @Id;
END
-- Get the next vendor.
FETCH NEXT FROM complex_cursor
INTO @Id, @curId, @curParentId, @curName
END
CLOSE complex_cursor;
DEALLOCATE complex_cursor;

[/syntax]

[syntax type="js"]

DECLARE @Id INT, @ChildQuestionText NVARCHAR(MAX)
DECLARE complex_cursor CURSOR FOR
SELECT Id, ChildQuestionText
FROM dbo.PersonalQuestionAnswer
WHERE QuestionId = 8
OPEN complex_cursor
FETCH NEXT FROM complex_cursor
INTO @Id, @ChildQuestionText
WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE PersonalQuestionAnswer SET ChildQuestionId = (SELECT ChildQuestionId FROM dbo.PersonalQuestionAnswer WHERE QuestionId = 7 AND [email protected]) WHERE Id = @Id

-- Get the next vendor.
FETCH NEXT FROM complex_cursor
INTO @Id, @ChildQuestionText
END
CLOSE complex_cursor;
DEALLOCATE complex_cursor;

Một số ví dụ về con trỏ (cursor) trong MSSQL Server

Category: Uncategorized
0
7088 views

Join the discussion

Your email address will not be published. Required fields are marked *