SQL For Loop, Using Cursor



/** Declare Variables For Cursor to Store Values **/
DECLARE @FirstName NVARCHAR(50), @Age INT, @ID NVARCHAR(50), @RegisterDate DATETIME

/** Confirm tempTable Is Deleted **/
DROP Table #TempTable1
DROP Table #TempTableName

/** Using Select Result Insert Value To Create A TempTable**/
SELECT * INTO #TempTableName
FROM
(
    /** Create a Table With Value Generated By My Self **/
    SELECT * FROM 
    (VALUES 
        /** First Value, Second Value In A Row**/
        ('David', 25),
        ('Brown', 26),
        ('Alex', 22)

    ) /** First Column Name, Second Column Name**/
    as E(FirstName, Age)
) N

SELECT * INTO #TempTable1
FROM 
(
    SELECT a.FirstName, a.Age, (right(NEWID(), 8)) as ID, (GETDATE()) as RegisterDate FROM #TempTableName a
) b

/*** SQL Using Cursor to Do a Foreach Loop, Load Every Row ***/
DECLARE Cursor1 CURSOR READ_ONLY
FOR
                    /** Data Source **/
                    SELECT * FROM #TempTable1

/* Put Data Into Cursor */
OPEN Cursor1

/* Put Data From Cursor Into Variables */
FETCH NEXT FROM Cursor1 INTO
@FirstName, @Age, @ID, @RegisterDate

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @newID nvarchar(50) = right(NEWID(), 8)
    PRINT CONCAT(@newID,  ' | ', @FirstName)

    /* Execute Insert */
    --INSERT INTO User_Info
    --                      (
    --                          FirstName, Age, ID, RegisterDate
    --                      )

    --                      VALUES
    --                      (
    --                          @FirstName, @Age, @ID, @RegisterDate
    --                      )

    /* Next Row,Put Value From Cursor Into Variables Again */
    FETCH NEXT FROM Cursor1 INTO
    @FirstName, @Age, @ID, @RegisterDate
END

/* Close Cursor */
CLOSE Cursor1

/* Release Cursor */
DEALLOCATE Cursor1
#SQL #cursor #for #foreach #Loop






你可能感興趣的文章

依賴注入與自動測試

依賴注入與自動測試

用 D3.js v4 看 Pokemon 屬性表

用 D3.js v4 看 Pokemon 屬性表

TypeScript 筆記:void 與 any

TypeScript 筆記:void 與 any






留言討論