Copy records using stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Copy records using stored procedure

Author

Topic

ccbuilder
Starting Member

17 Posts

Posted - 2012-06-01 : 14:43:13

I have the following database structure

Table DocumentLibrary
[DOCID] int identity(1,1), PK
[Cps] varchar(3),
[OrgName] varchar(30),
[Sy] varchar(9),
[DTS] Datetime,
[PlanType] varchar(10)

Table Goals
[GoalID] int identity(1,1), PK
[DOCID] int, FK
[Number] int,
[Description] varchar(250),
[DTS] Datetime

Table Objectives
[ObjID] int identity(1,1), PK
[GoalID] int, FK
[Number] int,
[Description] varchar(250),
[DTS] Datetime

Table strategy
[StrategyID] int identity(1,1), PK
[ObjID] int identity(1,1), FK
[Number] int,
[Description] varchar(250),
[DTS] Datetime

I would like to copy a record and all its related table records under a new DocID

I have created a Stored Procedure to do this:

Create Proc [dbo].[CopyPlan](@DocID int, @Cps varchar(3), @CpsName varchar(30), @sy varchar(9), @PlanType varchar(10))
AS

DECLARE @newDocID int, 
		@newGoalID int, 
		@GoalID int, 
		@newObjID int, 
		@ObjID int
		
INSERT INTO DocumentLibrary ([Cps],[OrgName],[Sy],[DTS],[Status],[Type],[CreatedBy],[PlanID])
	VALUES (@Cps,@CpsName,@sy,CURRENT_TIMESTAMP,'A',@PlanType,'john doe',1)

SET @newDocID = SCOPE_IDENTITY()

DECLARE CUR CURSOR LOCAL FOR
	SELECT GoalID FROM Goals WHERE DocID = @DocID

OPEN CUR
	FETCH NEXT FROM CUR INTO @GoalID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO Goals ([DocID],[Number],[Description],[DTS])
				    SELECT @newDocID, [Number],[Description],CURRENT_TIMESTAMP
					FROM Goals 
					WHERE GoalID=@GoalID
		SET @newGoalID = SCOPE_IDENTITY()		
										
		INSERT INTO Objectives([GoalID],[Number],[Description],[DTS])
			SELECT @newGoalID,[Number],[Description],CURRENT_TIMESTAMP
			FROM Objectives 
			WHERE GoalID = @GoalID			
		SET @newObjID = SCOPE_IDENTITY()
				
			DECLARE OBJ_CUR CURSOR LOCAL FOR 
				SELECT [ObjID] FROM Objectives WHERE GoalID=@GoalID
			OPEN OBJ_CUR
			FETCH NEXT FROM OBJ_CUR INTO @ObjID
			WHILE @@FETCH_STATUS = 0
			BEGIN			
				
				INSERT INTO Strategy([ObjID],[Number],[Description],[DTS])
						SELECT @newObjID,[Number],[Description],CURRENT_TIMESTAMP
						FROM Strategy 
						WHERE [ObjID] = @ObjID
				FETCH NEXT FROM OBJ_CUR INTO @ObjID
			END
			CLOSE OBJ_CUR
			DEALLOCATE OBJ_CUR
					
		FETCH NEXT FROM CUR INTO @GoalID
	END	
CLOSE CUR
DEALLOCATE CUR

It works to some degree except that once the stored procedure gets to the objectives it assigns the id of the last @newObjID
What I would like to do is at each iteration of the following

INSERT INTO Objectives([GoalID],[Number],[Description],[DTS])
			SELECT @newGoalID,[Number],[Description],CURRENT_TIMESTAMP
			FROM Objectives 
			WHERE GoalID = @GoalID			
		SET @newObjID = SCOPE_IDENTITY()

is to use the newly created id from the select statement so that it can be used by the Strategy table insertion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 00:30:17

you dont need cursor at all. you can use OUTPUT clause instead for this


Create Proc [dbo].[CopyPlan](@DocID int, @Cps varchar(3), @CpsName varchar(30), @sy varchar(9), @PlanType varchar(10))
AS

DECLARE @INSERTED_GOALS table
(
newGoalID int,
OldGoalID int
)


DECLARE @INSERTED_OBJ table
(
newObjID int,
OldObjID int
)




		
INSERT INTO DocumentLibrary ([Cps],[OrgName],[Sy],[DTS],[Status],[Type],[CreatedBy],[PlanID])
	VALUES (@Cps,@CpsName,@sy,CURRENT_TIMESTAMP,'A',@PlanType,'john doe',1)

SET @newDocID = SCOPE_IDENTITY()


					INSERT INTO Goals ([DocID],[Number],[Description],[DTS])
					OUTPUT INSERTED.GoalID,g.GoalID INTO @INSERTED_GOALS
				    SELECT @newDocID, [Number],[Description],CURRENT_TIMESTAMP
					FROM Goals g
					WHERE  DocID = @DocID
					
					
					
		
										
		INSERT INTO Objectives([GoalID],[Number],[Description],[DTS])
		 OUTPUT INSERTED.[ObjID],o.[ObjID] INTO @INSERTED_OBJ
			SELECT ig.newGoalID,[Number],[Description],CURRENT_TIMESTAMP
			FROM Objectives o
			INNER JOIN  @INSERTED_GOALS ig
			ON ig.OldGoalID = o.GoalID
				
						
				
		INSERT INTO Strategy([ObjID],[Number],[Description],[DTS])
		SELECT iob.newObjID,[Number],[Description],CURRENT_TIMESTAMP
		FROM Strategy s
		INNER JOIN @INSERTED_OBJ iob
		ON iob.[OldObjID] = s.[ObjID]
			
			
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ccbuilder
Starting Member

17 Posts

Posted - 2012-06-02 : 01:31:46

Thank you for your reply, I get the following error messages:

The multi-part identifier "g.GoalID" could not be bound.
The multi-part identifier "o.ObjID" could not be bound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 01:48:14

wat about this?


Create Proc [dbo].[CopyPlan](@DocID int, @Cps varchar(3), @CpsName varchar(30), @sy varchar(9), @PlanType varchar(10))
AS

DECLARE @INSERTED_GOALS table
(
newGoalID int,
OldGoalID int,
[Number] int,
[Description] varchar(250)
)


DECLARE @INSERTED_OBJ table
(
newObjID int,
OldObjID int,
[Number] int,
[Description] varchar(250)
)




		
INSERT INTO DocumentLibrary ([Cps],[OrgName],[Sy],[DTS],[Status],[Type],[CreatedBy],[PlanID])
	VALUES (@Cps,@CpsName,@sy,CURRENT_TIMESTAMP,'A',@PlanType,'john doe',1)

SET @newDocID = SCOPE_IDENTITY()


					INSERT INTO Goals ([DocID],[Number],[Description],[DTS])
					OUTPUT INSERTED.GoalID,INSERTED.[Number],INSERTED.[Description] INTO @INSERTED_GOALS (newGoalID,[Number],[Description])
				    SELECT @newDocID, [Number],[Description],CURRENT_TIMESTAMP
					FROM Goals g
					WHERE  DocID = @DocID
					
					
					UPDATE ig
					SET ig.oldGoalID=g.GoalID
					FROM @INSERTED_GOALS ig
					JOIN Goals g
					ON ig.Number = g.Number
					AND ig.Description = g.Description
					AND ig.newGoalID<> g.GoalID
		
										
		INSERT INTO Objectives([GoalID],[Number],[Description],[DTS])
		 OUTPUT INSERTED.[ObjID],INSERTED.[Number],INSERTED.[Description] INTO @INSERTED_OBJ(newObjID,[Number],[Description])
			SELECT ig.newGoalID,[Number],[Description],CURRENT_TIMESTAMP
			FROM Objectives o
			INNER JOIN  @INSERTED_GOALS ig
			ON ig.OldGoalID = o.GoalID
				
					UPDATE iob
					SET iob.oldObjID=o.[ObjID]
					FROM @INSERTED_OBJ iob
					JOIN Objectives o
					ON iob.Number = o.Number
					AND iob.Description = o.Description
					AND iob.newObjID <> o.[ObjID]	
				
		INSERT INTO Strategy([ObjID],[Number],[Description],[DTS])
		SELECT iob.newObjID,[Number],[Description],CURRENT_TIMESTAMP
		FROM Strategy s
		INNER JOIN @INSERTED_OBJ iob
		ON iob.[OldObjID] = s.[ObjID]
			
			
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ccbuilder
Starting Member

17 Posts

Posted - 2012-06-02 : 02:15:41

This is the error I get:

Ambiguous column name 'Number'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-02 : 13:42:46

add the aliases!


Create Proc [dbo].[CopyPlan](@DocID int, @Cps varchar(3), @CpsName varchar(30), @sy varchar(9), @PlanType varchar(10))
AS

DECLARE @INSERTED_GOALS table
(
newGoalID int,
OldGoalID int,
[Number] int,
[Description] varchar(250)
)


DECLARE @INSERTED_OBJ table
(
newObjID int,
OldObjID int,
[Number] int,
[Description] varchar(250)
)




		
INSERT INTO DocumentLibrary ([Cps],[OrgName],[Sy],[DTS],[Status],[Type],[CreatedBy],[PlanID])
	VALUES (@Cps,@CpsName,@sy,CURRENT_TIMESTAMP,'A',@PlanType,'john doe',1)

SET @newDocID = SCOPE_IDENTITY()


					INSERT INTO Goals ([DocID],[Number],[Description],[DTS])
					OUTPUT INSERTED.GoalID,INSERTED.[Number],INSERTED.[Description] INTO @INSERTED_GOALS (newGoalID,[Number],[Description])
				    SELECT @newDocID, g.[Number],g.[Description],CURRENT_TIMESTAMP
					FROM Goals g
					WHERE  DocID = @DocID
					
					
					UPDATE ig
					SET ig.oldGoalID=g.GoalID
					FROM @INSERTED_GOALS ig
					JOIN Goals g
					ON ig.Number = g.Number
					AND ig.Description = g.Description
					AND ig.newGoalID<> g.GoalID
		
										
		INSERT INTO Objectives([GoalID],[Number],[Description],[DTS])
		 OUTPUT INSERTED.[ObjID],INSERTED.[Number],INSERTED.[Description] INTO @INSERTED_OBJ(newObjID,[Number],[Description])
			SELECT ig.newGoalID,o.[Number],o.[Description],CURRENT_TIMESTAMP
			FROM Objectives o
			INNER JOIN  @INSERTED_GOALS ig
			ON ig.OldGoalID = o.GoalID
				
					UPDATE iob
					SET iob.oldObjID=o.[ObjID]
					FROM @INSERTED_OBJ iob
					JOIN Objectives o
					ON iob.Number = o.Number
					AND iob.Description = o.Description
					AND iob.newObjID <> o.[ObjID]	
				
		INSERT INTO Strategy([ObjID],[Number],[Description],[DTS])
		SELECT iob.newObjID,s.[Number],s.[Description],CURRENT_TIMESTAMP
		FROM Strategy s
		INNER JOIN @INSERTED_OBJ iob
		ON iob.[OldObjID] = s.[ObjID]
			
			
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ccbuilder
Starting Member

17 Posts

Posted - 2012-06-04 : 17:44:17

Thanks visakh16, this works.
I appreciate all your help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 21:25:25

welcome!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ccbuilder
Starting Member

17 Posts

Posted - 2013-09-05 : 15:28:49

Hi, thanks for the invaluable assistance you have provided me.
Something seems to be happening to the above script.
It seems that only certain rows are getting replaced with the correct old foreign key relation.
Originally I thought of using cursors to iterate thru every Goal so that I could copy each objective and each related strategy.
Initially I thought of iterating thru the records and insert the old GoalID into a GoalID Table, doing the same for each Objective and the same for each Strategy. As a last step the update of the foreign key would be done from this temp table.
My question is: modifying the above Stored Procedure would I be able to accomplish such task?
Thanks in advance for your assistance :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources