Cascade Inserts/Clone a tree

I am creating a small project management application using SQL Server. There
are three main tables (prjProjects, prjTasks, prjDetails). A project can
have 0 to many tasks and a tasks can have 0 to many details.
Many of our projects are very similar in that they would consist of
basically the same task and detail lists/records. I would like to be able to
"clone" a project using a stored procedure. I will be accessing the tables
from multiple front ends such as MS Access, ColdFusion, and ASP. A stored
procedure would allow me to create a new, cloned project from any of these
front ends.
I am not sure where to start with this. I have searched Google and news
groups without finding much useful information. Do I need to create a cursor
to loop through records and perform inserts or is there a more efficient
method? The tables, significant fields, and records are created below. There
are additional fields for descriptions, dates, etc but if I get the basic
framework, I should be able to include the other fields. I would like to
duplicate the "TITLE" field values in the new records.
Hope I have provided enough info that someone can point me in the right
direction.
Duane Hookom
MS Access MVP
CREATE TABLE [dbo].[prjProjects]
(
[PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjTasks] (
[TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
[TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TS_PR_ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjDetails]
(
[DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DT_TS_ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
(
[PR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
(
[TS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
(
[DT_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] ADD
CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
(
[TS_PR_ID]
) REFERENCES [dbo].[prjProjects] (
[PR_ID]
)
GO
ALTER TABLE [dbo].[prjDetails] ADD
CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
(
[DT_TS_ID]
) REFERENCES [dbo].[prjTasks] (
[TS_ID]
)
GO
INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
Two',2)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
Two',2)
GO"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
First you should be to add some important constraints. These are my
assumptions of course but the keys will be important here.
ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
UNIQUE (PR_TITLE);
ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
UNIQUE (TS_TITLE, TS_PR_ID);
ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
UNIQUE (DT_TITLE, DT_TS_ID);
Now create a proc something like this (error handling omitted for brevity):
CREATE PROC dbo.usp_project_copy
(
@.pr_id INT,
@.pr_title NVARCHAR(50),
@.new_pr_id INT OUTPUT
)
AS
BEGIN
INSERT INTO dbo.prjProjects (PR_TITLE)
VALUES (@.pr_title) ;
SET @.new_pr_id = SCOPE_IDENTITY();
INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
SELECT TS_TITLE, @.new_pr_id
FROM dbo.prjTasks
WHERE TS_PR_ID = @.pr_id ;
INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
SELECT D1.DT_TITLE, T2.TS_ID
FROM dbo.prjDetails AS D1
JOIN dbo.prjTasks AS T1
ON D1.DT_TS_ID = T1.TS_ID
AND T1.TS_PR_ID = @.pr_id
JOIN dbo.prjTasks AS T2
ON T1.TS_TITLE = T2.TS_TITLE
AND T2.TS_PR_ID = @.new_pr_id
AND T1.TS_TITLE = T2.TS_TITLE ;
RETURN
END
GO
DECLARE @.pr_id INT
EXEC dbo.usp_project_copy
@.pr_id = 1,
@.pr_title = 'New Project',
@.new_pr_id = @.pr_id OUTPUT ;
SELECT *
FROM prjProjects
WHERE PR_ID = @.pr_id ;
Thanks for including the DDL. Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You could create a script file, very similar to the DDL/DML you offered,
adding additional elements (as David indicated), and then execute the script
file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
If you were to use any variables in the script file, remember that when a GO
is encountered, the variable goes out of scope, so you would have to
re-create and initialize it again.
If you want to automate the process, the script file can be executed from a
SQL Agent Job, or even from a stored procedure.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
> CREATE TABLE [dbo].[prjProjects]
> (
> [PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjTasks] (
> [TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TS_PR_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjDetails]
> (
> [DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DT_TS_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
> CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
> (
> [PR_ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
> CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
> (
> [TS_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
> CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
> (
> [DT_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjTasks] ADD
> CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
> (
> [TS_PR_ID]
> ) REFERENCES [dbo].[prjProjects] (
> [PR_ID]
> )
> GO
> ALTER TABLE [dbo].[prjDetails] ADD
> CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
> (
> [DT_TS_ID]
> ) REFERENCES [dbo].[prjTasks] (
> [TS_ID]
> )
> GO
> INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> One',1)
> GO
>
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> Two',2)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> Two',2)
> GO
>|||David and Arnie,
Thanks much. This should provide a good starting point for me. All of my
projects, tasks, and details have start and expected dates associated so I
will need to include them. I'm fairly sure I can get this on my own. If not,
I'll be back.
Duane Hookom
MS Access MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OHES$RsmGHA.3796@.TK2MSFTNGP05.phx.gbl...
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> First you should be to add some important constraints. These are my
> assumptions of course but the keys will be important here.
> ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
> UNIQUE (PR_TITLE);
> ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
> UNIQUE (TS_TITLE, TS_PR_ID);
> ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
> UNIQUE (DT_TITLE, DT_TS_ID);
>
> Now create a proc something like this (error handling omitted for
> brevity):
> CREATE PROC dbo.usp_project_copy
> (
> @.pr_id INT,
> @.pr_title NVARCHAR(50),
> @.new_pr_id INT OUTPUT
> )
> AS
> BEGIN
> INSERT INTO dbo.prjProjects (PR_TITLE)
> VALUES (@.pr_title) ;
> SET @.new_pr_id = SCOPE_IDENTITY();
> INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
> SELECT TS_TITLE, @.new_pr_id
> FROM dbo.prjTasks
> WHERE TS_PR_ID = @.pr_id ;
> INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
> SELECT D1.DT_TITLE, T2.TS_ID
> FROM dbo.prjDetails AS D1
> JOIN dbo.prjTasks AS T1
> ON D1.DT_TS_ID = T1.TS_ID
> AND T1.TS_PR_ID = @.pr_id
> JOIN dbo.prjTasks AS T2
> ON T1.TS_TITLE = T2.TS_TITLE
> AND T2.TS_PR_ID = @.new_pr_id
> AND T1.TS_TITLE = T2.TS_TITLE ;
> RETURN
> END
> GO
> DECLARE @.pr_id INT
> EXEC dbo.usp_project_copy
> @.pr_id = 1,
> @.pr_title = 'New Project',
> @.new_pr_id = @.pr_id OUTPUT ;
> SELECT *
> FROM prjProjects
> WHERE PR_ID = @.pr_id ;
> Thanks for including the DDL. Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||As an additional piece of information.
I would have a stored procedure creates the script file. By so doing, then I
could pass in the changable pieces of data, the stored procedure could
re-create all variables after encountering a GO in the script, and the end
result is a comprehensive script file that does not require me to do a
'search and destroy' (I mean search are replace) with the chance of missing
some location where a value needed to be replaced.
I've done it with two different processes. One, just use PRINT statements
line by line, then copy the generated output into a new QA window and save
(or execute the script). Or, two, using OSQL, output the generated script to
a file. I find the first choice gives me greater control on formating,
readibility, etc.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:etEkjismGHA.4164@.TK2MSFTNGP03.phx.gbl...
> You could create a script file, very similar to the DDL/DML you offered,
> adding additional elements (as David indicated), and then execute the
> script file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
> If you were to use any variables in the script file, remember that when a
> GO is encountered, the variable goes out of scope, so you would have to
> re-create and initialize it again.
> If you want to automate the process, the script file can be executed from
> a SQL Agent Job, or even from a stored procedure.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>