1 CREATE PROCEDURE [dbo].[Pro_Copy] 2 @OLDJiFenSeriesId VARCHAR(100), 3 @NEWJiFenSeriesId VARCHAR(100) 4 AS 5 6 BEGIN 7 DECLARE @TAB TABLE( 8 [F_Id] [varchar](50) NOT NULL, 9 [F_ParentId] [varchar](50) NULL, 10 [JiFenSeriesId] [varchar](50) NULL, 11 [F_EnCode] [varchar](50) NULL, 12 [PerformanceConfigurationName] [varchar](50) NULL, 13 [PerformanceConfigurationMin] [decimal](18, 2) NULL, 14 [PerformanceConfigurationMax] [decimal](18, 2) NULL, 15 [F_IsTree] [bit] NULL, 16 [F_Layers] [int] NULL, 17 [F_SortCode] [int] NULL, 18 [F_DeleteMark] [bit] NULL, 19 [F_EnabledMark] [bit] NULL, 20 [F_Description] [varchar](500) NULL, 21 [F_CreatorTime] [datetime] NULL, 22 [F_CreatorUserId] [varchar](50) NULL, 23 [F_LastModifyTime] [datetime] NULL, 24 [F_LastModifyUserId] [varchar](50) NULL, 25 [F_DeleteTime] [datetime] NULL, 26 [F_DeleteUserId] [varchar](50) NULL 27 ) 28 INSERT INTO @TAB SELECT * FROM JiFenPerformanceConfiguration WHERE [JiFenSeriesId]=@OLDJiFenSeriesId 29 30 UPDATE @TAB SET F_Id= NEWID() 31 32 DECLARE MyCursor CURSOR 33 FOR SELECT F_ParentId FROM JiFenPerformanceConfiguration WHERE [JiFenSeriesId]=@OLDJiFenSeriesId 34 35 OPEN MyCursor 36 37 DECLARE @F_ParentId VARCHAR(50) 38 FETCH NEXT FROM MyCursor INTO @F_ParentId 39 WHILE @@FETCH_STATUS =0 40 BEGIN 41 --获取原始表的Name 找到他的上一级 42 DECLARE @ID NVARCHAR(50), 43 @NAME NVARCHAR(100) 44 SET @ID =(SELECT F_Id FROM JiFenPerformanceConfiguration WHERE [JiFenSeriesId]=@OLDJiFenSeriesId and F_Id=@F_ParentId) 45 SET @NAME =(SELECT PerformanceConfigurationName FROM JiFenPerformanceConfiguration WHERE [JiFenSeriesId]=@OLDJiFenSeriesId and F_Id=@F_ParentId) 46 IF(@ID<>'') 47 BEGIN 48 Update @TAB SET F_ParentId =(SELECT F_Id FROM @TAB WHERE PerformanceConfigurationName=@NAME) WHERE F_ParentId=@ID 49 END 50 FETCH NEXT FROM MyCursor INTO @F_ParentId 51 END 52 CLOSE MyCursor 53 DEALLOCATE MyCursor 54 UPDATE @TAB SET JiFenSeriesId=@NEWJiFenSeriesId 55 56 INSERT INTO JiFenPerformanceConfiguration SELECT * FROM @TAB 57 58 END
