SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Copy records using stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ccbuilder
Starting Member

USA
17 Posts

Posted - 06/01/2012 :  14:43:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/02/2012 :  00:30:17  Show Profile  Reply with Quote
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/

Go to Top of Page

ccbuilder
Starting Member

USA
17 Posts

Posted - 06/02/2012 :  01:31:46  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/02/2012 :  01:48:14  Show Profile  Reply with Quote
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/

Go to Top of Page

ccbuilder
Starting Member

USA
17 Posts

Posted - 06/02/2012 :  02:15:41  Show Profile  Reply with Quote
This is the error I get:
Ambiguous column name 'Number'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/02/2012 :  13:42:46  Show Profile  Reply with Quote
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/

Go to Top of Page

ccbuilder
Starting Member

USA
17 Posts

Posted - 06/04/2012 :  17:44:17  Show Profile  Reply with Quote
Thanks visakh16, this works.
I appreciate all your help :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2012 :  21:25:25  Show Profile  Reply with Quote
welcome!

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

Go to Top of Page

ccbuilder
Starting Member

USA
17 Posts

Posted - 09/05/2013 :  15:28:49  Show Profile  Reply with Quote
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 :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000