CREATE PROCEDURE [dbo].[Pro_Copy]
@OLDJiFenSeriesId VARCHAR(),
@NEWJiFenSeriesId VARCHAR()
AS BEGIN
DECLARE @TAB TABLE(
[F_Id] [varchar]() NOT NULL,
[F_ParentId] [varchar]() NULL,
[JiFenSeriesId] [varchar]() NULL,
[F_EnCode] [varchar]() NULL,
[PerformanceConfigurationName] [varchar]() NULL,
[PerformanceConfigurationMin] [decimal](, ) NULL,
[PerformanceConfigurationMax] [decimal](, ) NULL,
[F_IsTree] [bit] NULL,
[F_Layers] [int] NULL,
[F_SortCode] [int] NULL,
[F_DeleteMark] [bit] NULL,
[F_EnabledMark] [bit] NULL,
[F_Description] [varchar]() NULL,
[F_CreatorTime] [datetime] NULL,
[F_CreatorUserId] [varchar]() NULL,
[F_LastModifyTime] [datetime] NULL,
[F_LastModifyUserId] [varchar]() NULL,
[F_DeleteTime] [datetime] NULL,
[F_DeleteUserId] [varchar]() NULL
)
INSERT INTO @TAB SELECT * FROM JiFenPerformanceConfiguration WHERE [JiFenSeriesId]=@OLDJiFenSeriesId UPDATE @TAB SET F_Id= NEWID() DECLARE MyCursor CURSOR
FOR SELECT F_ParentId FROM JiFenPerformanceConfiguration WHERE [JiFenSeriesId]=@OLDJiFenSeriesId OPEN MyCursor DECLARE @F_ParentId VARCHAR()
FETCH NEXT FROM MyCursor INTO @F_ParentId
WHILE @@FETCH_STATUS =
BEGIN
--获取原始表的Name 找到他的上一级
DECLARE @ID NVARCHAR(),
@NAME NVARCHAR()
SET @ID =(SELECT F_Id FROM JiFenPerformanceConfiguration WHERE [JiFenSeriesId]=@OLDJiFenSeriesId and F_Id=@F_ParentId)
SET @NAME =(SELECT PerformanceConfigurationName FROM JiFenPerformanceConfiguration WHERE [JiFenSeriesId]=@OLDJiFenSeriesId and F_Id=@F_ParentId)
IF(@ID<>'')
BEGIN
Update @TAB SET F_ParentId =(SELECT F_Id FROM @TAB WHERE PerformanceConfigurationName=@NAME) WHERE F_ParentId=@ID
END
FETCH NEXT FROM MyCursor INTO @F_ParentId
END
CLOSE MyCursor
DEALLOCATE MyCursor
UPDATE @TAB SET JiFenSeriesId=@NEWJiFenSeriesId INSERT INTO JiFenPerformanceConfiguration SELECT * FROM @TAB END