Monday, March 19, 2012

Create procedure to insert records for a project

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul


if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS|||

Quote:

Originally Posted by ck9663

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS


------
No this is not one time and i have to insert this set of 11 records for each of the 40 projects i.e. 40 times.. so i need to be able to create a procedure where i can increment the value of task_taskid for each record and insert the corresponding field values.|||

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul


Try below Logic to create a procedure:

1. Have a Cursor that will hold task_name, task_id, task_budjetdollar, task_budjethours
2. have a counter variable initilized to 1
3. LOOP through 1181..1221 becuase u said u need to add for 40 projectids from 1181,1182 and so on
4. With a FOR LOOP, loop through CURSOR data, and for each record (task_name), insert into table with task_name,task_id and projectid(FOR Loop value) and task_outline_num = counter variable that you have declared before in the procedure
5. COMMIT
6. Increment the counter variable by 1
7. End the Cursor LOOP
8. Reset the counter variable to 1
9. End Outer FOR LOOP
10. End Procedure

No comments:

Post a Comment