Stored procedure to copy projects

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 2000 Forums

Transact-SQL (2000)

Stored procedure to copy projects

Author

Topic

ajarlson
Starting Member

8 Posts

Posted - 2004-04-30 : 13:44:23

I am working on a stored proc to copy existing projects into new projects, and those projects also have tasks associated with them that need to be copied. Each task can either be a parent or a child, the parent task has a task_id (primary key identity field) but a null parent_task_id and the children have the system generated task_id and the task_id of the parent in the parent_task_id field. Each project can have multiple parent tasks, and each parent task can have multiple children. No matter what I try, I come up against the fact that I HAVE to insert the parent tasks into the main table BEFORE I can get the new parent_task_id for the new children. So while I have a relationship in the old task table between the parents and the children and I can find that and implement it in a variety of ways, and I can do a select and select all the parents and children, I cannot figure out how to insert the new parents into the table and then restore the relationship between the new parents and the new children, (remember that the TASK_ID of the parent is a system generated number so I have no idea what it will be UNTIL it is inserted into the main table). Lets say that in my tasks table I have:

project_id task_id parent_task_id
2 1 null
2 2 1
2 3 1
2 4 null
2 5 4
2 6 4

and I do an insert into the tasks table where project id = 2 (using the variable to insert new project_id) and parent_task_id = null
now I would have (potentially)

project_id task_id parent_task_id
3 7 null
3 8 null

Now if I do an insert of the children I could tell it to insert where project_id = 2 (using the variable to insert new project_id)
and parent_task_id is NOT NULL
project_id task_id parent_task_id
3 7 null
3 9 ?
3 10 ?
3 8 null
3 11 ?
3 12 ?

but I cannot guarantee that the right child will be with the right parent at this point. Can anyone help? I was told to avoid using cursors as this will be called by a web app and they have problems with cursors sometimes but if that is the only solution then I could try it.

Thanks,
Anna Jarlson

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-30 : 15:30:36

Ok this sounds like an OLD_ID NEW_ID problem. I had a similar thing when I needed to copy tarrifs amongst different DB's.

You need an OLD_ID column adding to the end of your new task table, insert the old parent id into it from the old table as part of your parent insert.

Now you can join on the old to update the children to the new.

And if you understood all that you're a better man than I!

;-]... Quack Waddle

ajarlson
Starting Member

8 Posts

Posted - 2004-04-30 : 15:37:56

I did think of that, but I don't think they want that field hanging out in the table all the time. However, if I can't figure out how to loop these records into the table one at a time maintaining the parent child relationship without a cursor, I might just suggest that to them and see what they say.
Thanks!

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-30 : 16:10:05

Well I would tell them to greese it roll it and..

Well you should keep it simple if you can its a golden rule. There shouldn't be problem with an extra column, it doesn't have to appear in the front end anywhere.

Tell them they can have the extra column and it'l run like a greased weasle, or they can have no column and a cursor that runs like a geriatric elephant on a steep incline.

;-]... Quack Waddle

ajarlson
Starting Member

8 Posts

Posted - 2004-04-30 : 16:13:17

I guess I was just trying to figure this out because people kept telling me that it should be SO EASY to do with a loop even without a cursor, so I was beating my head against the wall trying to do it.
But you are right, easiest is best after all and it will work.
Thanks!

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-30 : 16:20:12

Good boy(oops just checked your profile girl that should be sorry DOH!) would'nt want you getting missled by developers. They work in loops, and in programming loops are easy. (I am one of those too)

But DB's are SET based theory, you can use WHILE to loop and pull the ID into a variable one at a time instead of a cursor. But it will NEVER be as fast as the update on a join, and will ALWAYS use more resources.

Its all about leveraging the software to run your business efficiently, that means simple, quick, fast, cheap. Not winning medals for 100's of lines of obscure spaghetti loop code.

But I sense you know all this anyway.

;-]... Quack Waddle

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-04 : 16:33:00

Ok if you have to use loops.

Step 1.
You need to create a temp table, and declare two variables to hold each old and new task id for the transfered parent.

Step 2.
Insert the parent tasks you need to transfer into the the temp table including there current (soon to become old) task id's.

Step 3.
start a while loop insert the first parent task, get its new id and transfer its child tasks.

Step 4.
delete that parent task from the temp table and get the next one

loop.

/*Step 1 then
=============*/
Create Procedure sp_TransferTasks
AS
Declare @currid int
Declare @newid int
declare @projid

set @projid = 2

create table #tasksq
(
currid int
)

/*Step 2
========*/
insert into #taskq(currid)
select taskid from oldtaskstable
where parent_id is null

/*Step 3
=======*/

While exists(select taskid from #taskq)
begin

select @currid = min(currid) from #taskq

insert into newtasktable(project_id) values(@projid)

set @newid = @@IDENTITY

insert into newtasktable(project_id,parent_id)
select @proj_id,@newid
from old tasktable
where parent_id = @currid and project_id = @projid

/*Step 4
========*/
delete from #taskq where currid = @currid
end

drop table #taskq

return

That shuld basically cover it, I have guessed some column names and stuff as I don't know your environment completely, so you will need to tweak the odd line here and there.

;-]... Quack Waddle

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-04 : 16:38:11

quote:
Originally posted by ajarlson

No matter what I try, I come up against the fact that I HAVE to insert the parent tasks into the main table BEFORE I can get the new parent_task_id for the new children.

Why do you need new Id's?

SET IDENTITY_INSERT ON

Brett

8-)

ajarlson
Starting Member

8 Posts

Posted - 2004-05-04 : 16:45:24

The table has three fields that I have to worry about, task_id (identity), parent_task_id (not identity - this is the task_id of the parent) and project_id (foreign key). So when we insert the new tasks we want the id to generate automatically, then pick it up (as he did above with the @@identity) and stick it into the parent_task_id of the children. I did something similar to his above but didn't use that currid, I'm looking into that one.
Thanks ALL it is great to find a place with so much friendly help.

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-04 : 16:50:48

quote:
Why do you need new Id's?

SET IDENTITY_INSERT ON

Thats ok, but if the old and new tables are both identity(1,1) there is danger of overlapping id's, as they're not globally unique.

;-]... Quack Waddle

ajarlson
Starting Member

8 Posts

Posted - 2004-05-04 : 16:55:36

Here is the solution I came up with and it seems to work very well although I also developed code around the creation of an old_id field so I can present both solutions:

(first insert into main project table is here but didn't include it)

SET @newprojectid = @@identity

-- Get list of parents
--Create a temp table holding all the parents of the @old_Project_id.
--if no parents exist this should be an empty table
select
task_id,
parent_task_id,
project_id,
task_description,
task_notes,
task_type_id,
priority_id
into #parents
from
akt_tasks
where
project_id = @old_project_id
and (parent_task_id is null or parent_task_id = 0);

-- For each parent do:
--Get the smallest task_id from the parents table and store in the @old_task_id variable
--if no parents exist this variable will be null
SET @old_task_id =
(SELECT MIN(task_id)
FROM #parents)

--if no parents exist do not enter the WHILE loop

WHILE @old_task_id IS NOT NULL
BEGIN

-- Add parent to table using @new_project_id
INSERT INTO akt_tasks(project_id, parent_task_id, task_description, task_notes, task_type_id, priority_id)
SELECT
@newprojectid,
0,
task_description,
task_notes,
task_type_id,
priority_id
FROM
#parents
WHERE
task_id = @old_task_id

--Get task of new parent and store in @new_task_id variable

SET @new_task_id = @@identity

-- Add children of the @old_task_id to akt_tasks table, using @new_task_id for the new parent_task_id
INSERT INTO akt_tasks (project_id, parent_task_id, task_description,task_notes, task_type_id, priority_id)
SELECT
@newprojectid,
@new_task_id,
task_description,
task_notes,
task_type_id,
priority_id
FROM
akt_tasks
WHERE
parent_task_id = @old_task_id

-- Delete parent from parents table
DELETE
FROM #parents
WHERE task_id = @old_task_id

--move on to next parent. If there are no remaining parents,
--@old_task_id will be null and the loop will not repeat

SET @old_task_id =
(SELECT MIN(task_id)
from #parents)

ajarlson
Starting Member

8 Posts

Posted - 2004-05-04 : 17:50:20

As a side note to this thread, I coded this SP two different ways, one with the loop and one with the extra column old_id. The extra column method simply inserts all the tasks and updates the parents based on the old_id. I ran a trace on both the SPs copying the same project to compare the resources used.
The loop had a total of 513 reads, duration of 47 and CPU of 46.
The old_id method with insert and update?
Total of 147 reads, duration and CPU both 0.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources