Ms SQL Server 游标嵌套 初始化数据
--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
posted on 2017-05-12 15:35 freeliver54 阅读(346) 评论(0) 编辑 收藏 举报