--TRUNCATE TABLE TAB_ROLE_FUNC;

--SELECT * FROM TAB_ROLE_FUNC;

DECLARE @FUNC_CODE VARCHAR(20), @ROLE_ID BIGINT,@ALLOW_FLAG_DEFAULT BIT = 1;

--外层游标

DECLARE CURSOR_ROLE CURSOR FOR

SELECT ROLE_ID FROM TAB_ROLE WHERE ACTIVE_IND = 1;

OPEN CURSOR_ROLE

FETCH NEXT FROM CURSOR_ROLE

INTO @ROLE_ID;

WHILE @@FETCH_STATUS = 0

BEGIN

                    --内层游标

                    DECLARE CURSOR_FUNC CURSOR FOR

                    SELECT FUNC_CODE FROM TAB_FUNC;

                    OPEN CURSOR_FUNC

                    FETCH NEXT FROM CURSOR_FUNC

                    INTO @FUNC_CODE;

                    WHILE @@FETCH_STATUS = 0

                    BEGIN

                        --判断新增

                        IF (NOT EXISTS(SELECT 1 FROM TAB_ROLE_FUNC WHERE ROLE_ID=@ROLE_ID AND FUNC_CODE=@FUNC_CODE ))

                        BEGIN

                            INSERT INTO TAB_ROLE_FUNC

                             (ROLE_ID    ,FUNC_CODE    ,ALLOW_IND ,CREATED_BY            ,CREATION_DATE        ,LAST_UPDATED_BY        ,LAST_UPDATE_DATE)

                             VALUES(@ROLE_ID,@FUNC_CODE,@ALLOW_FLAG_DEFAULT,N'[SYSTEM]',N'2017-05-10 12:00:00',N'[SYSTEM]',N'2017-05-10 12:00:00');

                        END

                        FETCH NEXT FROM CURSOR_FUNC

                        INTO @FUNC_CODE;

                    END

                    CLOSE CURSOR_FUNC

                    DEALLOCATE CURSOR_FUNC

    FETCH NEXT FROM CURSOR_ROLE

    INTO @ROLE_ID;

END

CLOSE CURSOR_ROLE

DEALLOCATE CURSOR_ROLE

GO