[Database] Sql Server 2022 obtains target data through logical processing through temporary tables and cursor traversal

2023, Week 39. Give yourself a goal, and then stick to it and you will always get something. If you don’t believe me, try it!
Today, a friend asked me about a problem with Sql Server data processing. I just wanted to review the knowledge about Sql Server temporary tables and cursors.

1. Demand point description

There is the following data set with 9 records. If the record before and after 001 is not 001, then the record before 001 and the record 001 itself will be taken.
If the next record of 001 is still 001, then take the last record of 001 and the record just before 001.

Work Center	Serial Number	Remarks
1001	1
1002	2	Take this Record
001	3	Get this record
1004	4
1008	5	Get this record
001	6	The first previous record that appears consecutively
001	7
001	8	Take this Record
1009	9

1) Query the data set

2) Target data set

2. Temporary table

In SQL Server, a temporary table is a special table used to store temporary data.
Temporary tables can be created during query execution and are only valid for the current session or connection.
They are useful for calculations and operations that require the storage of temporary data.

SQL Server provides two types of temporary tables: local temporary table (Local Temporary Table) and global temporary table (Global Temporary Table).

2.1. Local Temporary Table

A local temporary table is a table name starting with # and is only visible within the session in which it was created.
The table is automatically deleted when the session that created it ends. Other sessions cannot access this table.

Example to create a local temporary table:

CREATE TABLE #TempTable (
    ID INT,
    Name VARCHAR(50)
);

2.2. Global Temporary Table

A global temporary table is a table name that begins with ## and is visible in any session on the server instance that created it.
The table is automatically deleted when all sessions referencing the table end.

Example to create a global temporary table:

CREATE TABLE ##TempTable (
    ID INT,
    Name VARCHAR(50)
);

When using a temporary table, you can insert, update, delete, and query data just like any other table.

It is worth noting that the structure of a temporary table (including column definitions and constraints) is similar to that of a permanent table, and objects such as indexes and triggers can be created.
However, when the session ends, the temporary table and its related objects are automatically cleaned and deleted.

Temporary tables are useful for temporary data storage and processing, such as staging intermediate results during complex queries and stored procedures or storing temporary data that needs to be shared across multiple queries or operations.

3. Cursor

In SQL Server, a cursor is a database object used to traverse a result set.
It provides a mechanism for processing query results row by row and can be used when data needs to be manipulated row by row.

Here are the general steps for using cursors:

3.1. Declare cursor

Declare a cursor using the DECLARE CURSOR statement and specify the name of the cursor and the query to traverse.

DECLARE CursorName CURSOR FOR
    SELECT Column1, Column2
    FROM TableName;

3.2. Open the cursor

Use the OPEN statement to open the cursor and prepare to start traversing the result set.

OPEN CursorName;

3.3. Obtain data

Use the FETCH NEXT statement to obtain a row of data at the current cursor position and store it in a variable. You can use the INTO clause to store data into multiple variables.

FETCH NEXT FROM CursorName INTO @Variable1, @Variable2;

3.4. Processing data

Process the obtained row data in a loop. This can be performing operations on the data, updating, deleting, etc., or simply outputting the data.

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Data processing
    -- For example, perform some operations or output data
    
    FETCH NEXT FROM CursorName INTO @Variable1, @Variable2;
END;

3.5. Close and release cursor

Use CLOSE to close the cursor, setting the cursor’s status to unusable, but not deleting the cursor. Finally, use DEALLOCATE to release the cursor and delete it from memory.

CLOSE CursorName;
DEALLOCATE CursorName;

Precautions:

Consider performance and resource usage when using cursors, as cursors can cause performance degradation and consume large amounts of memory.
After processing is complete, be sure to close and release the cursor to release resources.
You can use the @@FETCH_STATUS system variable to determine whether there are more rows to traverse.

The use of cursors needs to be carefully considered and used only when necessary. Try to use set operations instead of cursors to improve performance.

4. Solution

Based on the temporary tables and cursors learned above and combined with the requirements, the following logical operations can be performed to obtain the target query results.

code show as below

-- Create a local temporary table
if object_id('tempdb..#myTempTable') is not null begin
    drop table #myTempTable
end
/*else begin
    create table #myTempTable(
        workcenter varchar(50),
        serial number int
    )
end*/

-- When the temporary table does not exist
select * into #myTempTable from(
    select '1001' work center, 1 serial number union all
    select '1002' work center, 2 serial number union all
    select '001' work center, 3 serial number union all
    select '1004' work center, 4 serial number union all
    select '1008' work center, 5 serial number union all
    select '001' work center, 6 serial number union all
    select '001' work center, 7 serial number union all
    select '001' work center, 8 serial number union all
    select '1009' work center, 9 serial number
) a

-- select * from #myTempTable

-- Definition of becoming a wolf
declare @workcenter varchar(50)
declare @serial number int
declare @previous work center varchar(50)
declare @previous serial number int
set @previous work center='#'

-- Define cursor
declare cursorName cursor for
select work center, serial number from #myTempTable

-- Open cursor
open cursorName

-- Traverse the cursor
fetch next from cursorName into @workcenter,@serial number;
while @@fetch_status=0 begin
    -- print(@workcenter)

    if @previous work center='#' begin
        set @previous work center=@work center
        set @previous serial number=@serial number
    end
    else begin
        if @workcenter='001' begin
            if @previous work center!='001' begin
                -- Output a record on 001
                print(@Previous work center + ',' + convert(varchar(50),@Previous serial number))
            end
        end
        else begin
            if @previous work center!=@work center and @previous work center='001' begin
                -- Output the last item of 001
                print(@Previous work center + ',' + convert(varchar(50),@Previous serial number))
            end
        end

        set @previous work center=@work center
        set @previous serial number=@serial number
    end

    -- When traversing the next item, be sure to add this sentence, otherwise it will keep looping
    fetch next from cursorName into @workcenter,@serial number;
end

-- Close and destroy the cursor
close cursorName
deallocate cursorName

The effect is as follows

Directory