DECLARE @mergedate datetime
DECLARE @masterid int
DECLARE @duplicateid int
Select @mergedate = getdate ()
Declare merge_cursor cursor Fast_forward for select Mastercustomerid, Duplicatecustomerid from Duplicatecustomers where I smerged = 0
--Define a Cursor object [Merge_cursor]
--the cursor contains the result of the query [select Mastercustomerid, Duplicatecustomerid from duplicatecustomers where ismerged = 0].
Open Merge_cursor
--Open cursor
FETCH NEXT from Merge_cursor into @masterid, @duplicateid
--take data to a temporary variable
While @ @fetch_status = 0-system @ @fetch_status = 0 o'clock Loop End
--Doing the recycling process
Begin
exec mergeduplicatecustomers @masterid, @duplicateid
Update Duplicatecustomers
Set
ismerged = 1,
Mergedate = @mergedate
where
Mastercustomerid = @masterid and
Duplicatecustomerid = @duplicateid
FETCH NEXT from Merge_cursor into @masterid, @duplicateid
--Take the value again
End
Close Merge_cursor
--Close cursor
Deallocate merge_cursor
--Delete cursor
Technical implementation of cursor interpretation
To learn the cursors in Msqsql we need to create a data table to simulate the data:
Create a datasheet student, which contains 4 columns
CREATE TABLE Student
(
ID int identity (1,1) primary key,--Auto grow number
Name varchar NOT NULL--name
Age int is not null--ages
Address varchar (+) NOT null--addresses
)
There are 4 procedures for using cursors: declaring cursors, opening cursors, fetching data, closing cursors.
Declare PCURR cursor for--declares a cursor named Pcurr
declare @customer nvarchar (50)
DECLARE @age int
Select Name,age from student-This query result is very important, this is the dataset we need to read with the cursor
Open Pcurr--opening cursors
FETCH NEXT from Pcurr into @customer, @age
while (@ @fetch_status = 0)--Determines whether the cursor is read, reads over, and returns 100 instead of 0
Begin
Print (@customer)
Print (@age)
FETCH NEXT from Pcurr into @customer, @age--values, and since our comprehensive result set returns a 2-line record, we need 2 custom parameters to fetch the result set
Close Pcurr--closing cursors
Deallocate Pcurr--Releasing cursors
The difference between close and deallocate:
Close is the closing cursor
, this time the cursor still exists, we can open again, Deallocate is the release of the cursor, this time the cursor does not exist, if we need to use the cursor, we do not have to declare a