DECLARE @Test TABLE
( testRegion VARCHAR(10) not null)INSERT INTO @Test (testRegion)
SELECT 'A7' UNION ALL
SELECT 'A8' UNION ALL
SELECT 'A9' UNION ALL
SELECT 'A1' UNION ALL
SELECT 'A2' UNION ALL
SELECT 'A3' UNION ALL
DECLARE @currRegion VARCHAR(10)
DECLARE cur_Region CURSOR FORWARD_ONLY
FOR
SELECT testRegion FROM @Test
OPEN cur_ Region
FETCH NEXT cur_ Region INTO @currRegion
WHILE @@fetch_status = 0
BEGIN
INSERT INTO t1 (
depetId
, CategoryId
,regionValue
,PorF
,Enabled )
SELECT 90,7,@ currRegion,'P',1
UNION ALL
SELECT 91,7,@currRegion,'P',1
UNION ALL
SELECT 92,7,@ currRegion,'P',1
UNION ALL
SELECT 93,7,@ currRegion,'P',1
UNION ALL
SELECT 94,7,@ currRegion,'F',1
UNION ALL
SELECT 1,7,@ currRegion,'F',1
UNION ALL
SELECT 26,7,@ currRegion,'F',1
UNION ALL
SELECT 31,7,@ currRegion,'F',1
FETCH NEXT FROM cur_ Region INTO @ currRegion END
我想把 select 90, select 91...这块儿也用游标实现,不知道可不可以?
SELECT depetId , CategoryId , regionValue , PorF , ENABLED
FROM (
SELECT 90 AS depetId , 7 AS CategoryId , 'P' AS PorF , 1 AS Enabled
UNION ALL
SELECT 91 , 7 , 'P' , 1
UNION ALL
SELECT 92 , 7 , 'P' , 1
UNION ALL
SELECT 93 , 7 , 'P' , 1
UNION ALL
SELECT 94 , 7 , 'F' , 1
UNION ALL
SELECT 1 , 7 , 'F' , 1
UNION ALL
SELECT 26 , 7 , 'F' , 1
UNION ALL
SELECT 31 , 7 , 'F' , 1
) a ,
(
SELECT 'A7' AS testRegion
UNION ALL
SELECT 'A8'
UNION ALL
SELECT 'A9'
UNION ALL
SELECT 'A1'
UNION ALL
SELECT 'A2'
UNION ALL
SELECT 'A3'
) b应该是这样的,你这个用游标确实很不合适。
FETCH NEXT cur_ Region
这个为什么不能用游标啊,如果不用游标对A7 要插8条记录到这个表,同理A8...每一个catagoryId都要插8条相同的记录,所以我把catagoryId 放到游标里,数量也不大,用游标会有什么问题?