Create procedure to insert records for a project

[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 Management 1204 1180 0.1
02 - Installation 1207 1180 2
03 - Design Pilot 1208 1180 3
04 - Integration & programming 1209 1180 4
05 - Forms & reports 1210 1180 5
06 - Training 1211 1180 6
07 - Documentaion 1212 1180 7
08 - Data Take on 1213 1180 8
09 - Go Live Spt 1214 1180 9
10 - Post Go Live Spt 1215 1180 10
11 Other Out Of Scope 1216 1180 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

[ck9663]

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 Management 1204 1180 0.1
02 - Installation 1207 1180 2
03 - Design Pilot 1208 1180 3
04 - Integration & programming 1209 1180 4
05 - Forms & reports 1210 1180 5
06 - Training 1211 1180 6
07 - Documentaion 1212 1180 7
08 - Data Take on 1213 1180 8
09 - Go Live Spt 1214 1180 9
10 - Post Go Live Spt 1215 1180 10
11 Other Out Of Scope 1216 1180 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

[PRAW]

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.

[amitpatel66]

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 Management 1204 1180 0.1
02 - Installation 1207 1180 2
03 - Design Pilot 1208 1180 3
04 - Integration & programming 1209 1180 4
05 - Forms & reports 1210 1180 5
06 - Training 1211 1180 6
07 - Documentaion 1212 1180 7
08 - Data Take on 1213 1180 8
09 - Go Live Spt 1214 1180 9
10 - Post Go Live Spt 1215 1180 10
11 Other Out Of Scope 1216 1180 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