Cursors for SQL Server

--Cursors in SQL Server

--Declaring cursors

/*

declare cursorname [insensitive] [scroll] Cursor

For <select-query blocks >

[For {read only|update[of< column name >[,..., n]]}]

Insensitive means that the extracted data is stored in a temporary table created in the tempdb library, and any operation through this cursor will be performed in this temporary list. All changes to the base table are not reflected in the operation with the cursor, and any action that the user makes to the base table will be reflected in the cursor instead of that keyword.

Scroll: Specifies that all extraction options (First,last,prior,wext,relative,absolute) are available, allowing for deletion and updating (assuming no insensitive option is used)

For Read only: Cursor is read-only. Updates to data through read-only cursors are not allowed.

For update[of < column name >[,..., N]]: cursors are modifiable. Defines the columns that can be updated in this cursor. If the [of< column name >[,... N]] is defined, only the columns can be modified; otherwise, all the columns in the cursor can be modified.

*/

declare @tagname nvarchar (max)

DECLARE @max_datetime datetime

DECLARE mycursor cursor

For

Select A.tagname,max (IsNull (b.datetime,0))

From Tblfminfolive A

Left join Live_dblink.gb_scada_live.dbo.LiveData B in A.tagname=b.tagname and b.provider= ' tax '

Group BY A.tagname;

--Open cursor

Open mycursor;

--Extracting data

--Fetch[[next|prior|first|last|absoute n|relative n] from] < cursor name > [into @ variable name [,..., N]]

FETCH NEXT from MyCursor to @tagname, @max_datetime;

/* Global variable @ @fetch_status indicates the state of the FETCH statement

0 means extraction is normal.

1 indicates that the end of the data set has been taken

Other values indicate a problem with the operation

*/

--Into the loop

while (@ @fetch_status =0)

BEGIN

IF Right (@tagname, 2)! = ' YL '

--write Flux,plustotalflux,reversetotalflux to target table

INSERT INTO Live_dblink.gb_scada_live.dbo.LiveData (

Provider,sitename,tagname,metername,datetime,value)

Select ' Tax ', b.username,b.tagname,b.metername,a.readtime

, Case right (@tagname, 2)

When the ' SL ' then A.flux

When ' ZL ' then A.plustotalflux

When ' FL ' then A.reversetotalflux

End

From Tblfmreaddata A, tblfminfolive b

WHERE A.fmaddress+right (@tagname, 3) [email protected] and [email protected]

and a.readtime> @max_datetime

ELSE

--Write pressure to target table

INSERT INTO Live_dblink.gb_scada_live.dbo.LiveData (

Provider,sitename,tagname,metername,datetime,value)

Select ' Tax ', b.username,b.tagname,b.metername,a.readtime,isnull (a.pressure,0)

From tblfmreaddatapress a,tblfminfolive b

WHERE A.fmaddress+right (@tagname, 3) [email protected] and [email protected]

and a.readtime> @max_datetime

--Get the next piece of data

FETCH NEXT from Mycusor to @tagname, @max_datetime;

END;

--Close cursor

Close MyCursor;

--Releasing cursors

Deallocate mycursor;

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------

---The cursor failed to execute successfully due to an unknown reason for SQL Server, a temporary table was created, similar to the cursor, as follows:

--------------------------------------------------------------------------------

DECLARE @cun int

DECLARE @maxcun int

DECLARE @max_datetime datetime

DECLARE @fmaddress varchar (40)

CREATE TABLE #tmp_fmaddress

(

PID int identity (+),

fmaddress varchar (100),

Max_datetime datetime

)

Insert into #tmp_fmaddress (fmaddress,max_datetime)

Select A.fmaddress,max (IsNull (b.datetime,0))

From Tblfminfolive A

Left join Live_dblink.gb_scada_live.dbo.LiveData B in A.tagname=b.tagname and b.provider= ' tax '

and B.datetime>dateadd (Day,-2,getdate ())

GROUP BY A.fmaddress

Set @cun =1

Select @maxcun =max (PID) from #tmp_fmaddress

--Into the loop

While @cun < @maxcun +1

BEGIN

Select @fmaddress =fmaddress, @max_datetime =max_datetime from #tmp_fmaddress where [email protected]

------Traffic Data

Select Fmaddress,readtime,flux,plustotalflux,reversetotalflux

Into #temp_flux

From Tblfmreaddata WHERE [email protected]

and readtime> @max_datetime

and Readtime>dateadd (Day,-2,getdate ())

-----Pressure Data

Select Fmaddress,readtime, IsNull (pressure,0) Press

Into #temp_press

From tblfmreaddatapress WHERE [email protected]

and readtime> @max_datetime

and Readtime>dateadd (Day,-2,getdate ())

--write Flux,plustotalflux,reversetotalflux to target table

INSERT INTO Live_dblink.gb_scada_live.dbo.LiveData (

Provider,sitename,tagname,metername,datetime,value)

Select ' Tax ', b.username,b.tagname,b.metername,a.readtime

, Case right (b.tagname,2)

When the ' SL ' then A.flux

When ' ZL ' then A.plustotalflux

When ' FL ' then A.reversetotalflux

End

From #temp_flux a,tblfminfolive b

where a.fmaddress=b.fmaddress and [email protected]+ ' _yl '

--Write pressure

INSERT INTO Live_dblink.gb_scada_live.dbo.LiveData (

Provider,sitename,tagname,metername,datetime,value)

Select ' Tax ', b.username,b.tagname,b.metername,a.readtime,a.press

From #temp_press a,tblfminfolive b

where a.fmaddress=b.fmaddress and [email protected]+ ' _yl '

drop table #temp_flux

drop table #temp_press

--Get the next piece of data

Set @[email protected]+1

END

Cursors for SQL Server