March 12, 2003 at 8:27 pm
I have a scenario that I am having problem implementing.
I have 4 tables:
tblproject, tbltask, tblstatus and tblprojectTasks.
tblProjects has:
project_ID int identity,
projName varchar(50),
assignedTo varchar(50),
startDate datetime,
endDate datetime (these are just the minimum fields)
tblstatus has:
statusID int (NOT identity),
statusName varchar(50)
tblTask has:
taskID int (NOT identity),
taskName varchar(50)
Then I have tblprojectTasks has:
projectTask_ID int identity,
project_id int (from tblproject table),
taskID int (from tblTask)
statusID int (from tblstatus)
Here is my problem.
Tbltask table has pre-populated values.
for instance taskname from tbletask table has 8 taskID numbers from taskID1 through taskID8. Each taskID represents a project lifecycle and each lifecycle is called a taskname.
For instance:
taskID1 has a taskname called define problem, taskID2 has prototype, taskID3 has analysis, taskID4 has documentation, taskID 5 has programming, taskID6 has deployment, taskID7 has train, taskID8 has maintenane.
Every new project has those 8 taskids and tasknames. The tasknames do not change. Tasknames are always 8.
Any time a project is created, there are these 8 tasknames associated with that project. In other words, for a project to be considered complete, that project must go through 8 tasknames ( we call this project lifecylce). You cannot jump to the next taskname until current taskname is completed.
Tblstatus table also has pre-populated values.
statusId1 = 'not started'
statusid2 = 'on hold'
statusid3 = 'in progress'
statusid4 = 'almost done'
statusid5 = 'complete'
What I want to do is first create a project.
Then use these method:
/* Move the identity value of the new record into a variable */
SET @ProjectID = @@IDENTITY
to assign a new projectId to the tblprojecttask table.
this way, project_Id from tblproject table can be automatically inserted into tblprojectTask table whenever one is created in the tblproject table.
This I have done correctly.
I also want to insert those 8 taskids from tbltask table into tblprojecttask
for each projectId inseted.
And then I want to insert the 5 statusIds from tblstatus table
I would like to do this using a while loop like:
While not EOF
TaskID = @taskid
StatusID = 1 -- All the new tasks have to be set to the starting statusID
INSERT INTO tblProjectTasks (Project_id, TaskID, StatusID) VALUES (@ProjectID, TaskID, StatusID)
Move to Next statusid
end
My question is giving that the taskID from tbltask and statusid from tblstatus are not an identity, how do I look through those two tables inserting their respective ids into the tblprojecttask table so that each new project will have them?
Below is the stored proc I am trying to construct:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsertGISProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsertGISProject]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Procedure spInsertGISProject(
@requestorname varchar(50),
@division varchar(50),
@dueDate datetime,
@projName varchar(50),
@justification varchar(2000),
@mapTitle varchar(50),
@reqDate datetime,
@description varchar(4000),
@EstCompDate datetime,
@Assigned varchar(50),
@Priority varchar(50),
@Purpose varchar(500),
@Geographic varchar(200),
@name varchar(50))
AS
Declare
@ProjectID int,
@TaskID int,
@StartDate datetime,
@EndDate datetime,
@DailyNotes varchar(2000),
@TaskName varchar(50),
@Issues varchar(2000),
@Service_Type varchar(50)
/* Move the identity value of the new record into a variable */
/* Create and populate new Project record */
Insert into tblProjects(
Section,
requestorName,
division,
startDate,
projName,
justification,
mapTitle,
requestedCompletionDate,
email,
description,
estimatedCompletionDate,
AssignedTo,
Priority,
Purpose,
Geographic,
fullName,
Manager,
PR_Percent
)
VALUES(
'GIS',
@requestorName,
@division ,
@dueDate,
@projName,
@justification,
@mapTitle,
@reqDate ,
'mandarin@yahoo.com',
@description,
@EstCompDate,
@Assigned,
@Priority,
@Purpose,
@Geographic,
@name,
'mandarin',
0)
/* Move the identity value of the new record into a variable */
SET @ProjectID = @@IDENTITY
/* Create and populate new Task record */
Insert INTO tblProjectTasks(
Project_ID,
TaskCode,
StartDate,
EndDate,
DailyReport,
TaskName,
Issues,
Service_Type
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
March 13, 2003 at 3:00 am
If you want to insert into tblProjectTasks all the combinations of taskID and statusID then you can use
INSERT INTO tblProjectTasks
SELECT @ProjectID,
t.taskID,
s.statusID
...
FROM tblTask t
CROSS JOIN tblstatus s
Far away is close at hand in the images of elsewhere.
Anon.
March 13, 2003 at 6:08 am
Hi David!
A couple of follow up questions, please.
1, does it matter that @projectID is coming from tblproject table and is an identity?
2, what will be whereclause here for this:
SELECT @ProjectID,
t.taskID,
s.statusID
...
FROM tblTask t
CROSS JOIN tblstatus s
given my table structure, and
3, will this join capture all the pre-polulated data in both tblstatus and tbltask tables?
sorry if this question appears too novice.
thanks for your response
March 13, 2003 at 6:23 am
1. No. The data types are int and you can do what you like with them. IDENTITY is only used to create new number (int) when a row is inserted.
2. Not sure what you mean here. Do you want to reduce the data selected?
3. Yes. CROSS JOIN will give you all the rows from both tables. eg
taskID statusID
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
1 2
2 2
3 2
4 2
5 2
6 2
7 2
8 2
etc
Far away is close at hand in the images of elsewhere.
Anon.
March 13, 2003 at 7:47 am
you answered my questions, thank you!
One last one and I promise to leave you alone.
I am setting the initial values from the tblstatus table to 'not started' which has an ID of 1.
Will this do it:
INSERT INTO tblProjectTasks(poject_Id, taskID, statusID)
SELECT @ProjectID,
t.taskID,
1
FROM tblTask t
CROSS JOIN tblstatus s
March 13, 2003 at 8:36 am
use either
SELECT @ProjectID,t.taskID,1
FROM tblTask t
or
SELECT @ProjectID,t.taskID,s.statusID
FROM tblTask t
CROSS JOIN tblstatus s
WHERE s.statusID = 1
First option will be faster as you do not need extra join but the second will only insert records where statusID 1 exists in table tblstatus.
Far away is close at hand in the images of elsewhere.
Anon.
March 13, 2003 at 10:22 am
thanks a bunch for your help!
It is really appreciated.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply