Foreach loop through the results of query

December 24, 2022

I have a query that returns list of userid based on below query

SELECT DISTINCT [AUTHORIZATION_NAME] 
INTO #TEMP 
FROM [saving].[SAV_AUTHORIZATION] WHERE [ACTIVE_FLAG] = 1 AND [SAV_AUTHORIZATION_TYPE_ID] = 4

Now for each user in #TEMP I want to insert new row into other table how to do that?

BEGIN
            INSERT INTO [saving].[SAV_INITIATIVES_APPROVAL]
            (
                [SAV_INITIATIVES_ID],
                [APPROVAL_DATE_SUBMITTED],
                [APPROVAL_BY_NM],
                [APPROVAL_BY_DT],
                [LAST_UPDATE_DATE],
                [LAST_UPDATE_USER],
                [ACTIVE_FLAG]
            )
            VALUES (
                @ID,
                GETDATE(),
                @APPROVER_NM, //// here i want foreach username
                NULL,
                GETDATE(),
                @LAST_UPDATE_USER,
                1
            )
        END

>Solution :

You could use an insert-select statement, where you query any values that don’t come from the original table:

INSERT INTO [saving].[SAV_INITIATIVES_APPROVAL]
(
    [SAV_INITIATIVES_ID],
    [APPROVAL_DATE_SUBMITTED],
    [APPROVAL_BY_NM],
    [APPROVAL_BY_DT],
    [LAST_UPDATE_DATE],
    [LAST_UPDATE_USER],
    [ACTIVE_FLAG]
)
SELECT
    DISTINCT @ID,
    GETDATE(),
    [AUTHORIZATION_NAME], 
    NULL,
    GETDATE(),
    @LAST_UPDATE_USER,
    1
FROM 
    [saving].[SAV_AUTHORIZATION] 
WHERE
    [ACTIVE_FLAG] = 1 AND [SAV_AUTHORIZATION_TYPE_ID] = 4