insert values of table and its dependencies

Hello,

I have 3 table
Table 1 : list of "whatever" program
Table 2: list of tasks for each program
Table 3: list of user for each task

When I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.

Is there a way to do that in sql?
I do not really want to go through each single task, then copy it with the new program, then get the @.@.identity of the inserted task and then assign the same user to the newly inserted task.

ThanksI am really stock with that, any idea?

Thanks very much|||

I am assuming something like this for a db structure...

TABLE 1:

ProjectID int identity(1,1)
ProjectName varchar(50)

TABLE 2:

TaskID int identity(1,1)
ProjectID int,
TaskName varchar(50)

TABLE 3:

UserTaskID int identity(1,1)
TaskID int
UserName varchar(50)

If so, you could do something like the following:

DECLARE

@.NewProjectint
DECLARE @.ProjectToCopyint
SET @.ProjectToCopy= 1

INSERT

INTO TABLE1
(
ProjectName
)
VALUES
(
'New Project Name'
)

SELECT

@.NewProject=SCOPE_IDENTITY()

INSERT

INTO TABLE2
(
ProjectID,
TaskName
)
SELECT @.NewProject, TaskNameFROM TABLE2WHERE ProjectID= @.ProjectToCopy
INSERTINTO TABLE3
(
TaskID
,UserName
)
SELECT t1.TaskID, UserName
FROM TABLE2 t
INNERJOIN TABLE3 u
ON t.TaskID= u.TaskID
INNERJOIN TABLE2 t1
ON t.TaskName= t1.TaskName
AND t1.ProjectID= @.NewProject
WHERE t.ProjectID= @.ProjectToCopy

What do you think?

Steve

|||Thanks, I will give it a try