SQLSERVER 复制同一张表的递归结构

 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

相关阅读:
解析“0”的读法
CM3 支持 64 位整数, LDRD STRD
segger usbh struct
BCM2046 Bluetooth on new 8,3 MacBook Pro USB Interface Descriptor bAlternateSetting
STM32 USB Host Library 学习笔记 (2) USBH_InterruptSendData() USBH_ClrFeature()
Double Link List
汉语拼音方案里的O（哦）和 UO（窝）
git commit 时，会打开默认的文本编辑器，要求你输入提交信息
WIN7 常见问题及解决方法
git 创建 .gitignore 文件建立项目过滤规则

原文地址：https://www.cnblogs.com/yuming1983/p/6212481.html