Sorry to post this again but I really am lost with it and do need help.
I have data in a MDB table I need to use to update a SQL table. I have imported it into SQL with A DTS package. So far all is good. I then need to update another SQL table and continue the row_id's that are already in the SQL table.
MDB->SQL[jm_update]-> + [row_id] -> [job_charges]
I created the following code and thought it was working. It works when I am only importing a couple rows at a time but when I import more than 4 or 5 rows it begins making mistakes. It will delete a row and duplicate another. the number of errors grows as the number of rows being imported grows. It always returns the correct number of rows, If I was importing 100 rows it will give me 100 rows, it is just that 5 or 8 will be deleted and or duplicated.
If someone can see what I have wrong or do I need to take a different tack or ???
CREATE PROCEDURE [update_JM_Updatetbl]
( @charge_date_1 [datetime],
@charge_time_2 [datetime],
@job_number_3 [int],
@job_name_4 [nvarchar](40),
@charge_date_5 [datetime],
@charge_time_6 [datetime],
@start_time_7 [datetime],
@end_time_8 [datetime],
@department_code_9 [varchar](50),
@cost_center_10 [varchar](16),
@employee_number_11 [int],
@work_classification_12 [varchar](3),
@task_code_13 [varchar](16),
@task_type_14 [varchar](16),
@task_name_15 [varchar](80),
@note_16 [varchar](254),
@qty_17 [numeric],
@unit_name_18 [varchar](20),
@unit_cost_19 [numeric],
@total_cost_20 [numeric])
AS UPDATE [SMS_1_0].[dbo].[JM_Updatetbl]
SET [job_number] = @job_number_3,
[job_name] = @job_name_4,
[charge_date] = @charge_date_5,
[charge_time] = @charge_time_6,
[start_time] = @start_time_7,
[end_time] = @end_time_8,
[department_code] = @department_code_9,
[cost_center] = @cost_center_10,
[employee_number] = @employee_number_11,
[work_classification] = @work_classification_12,
[task_code] = @task_code_13,
[task_type] = @task_type_14,
[task_name] = @task_name_15,
[note] = @note_16,
[qty] = @qty_17,
[unit_name] = @unit_name_18,
[unit_cost] = @unit_cost_19,
[total_cost] = @total_cost_20
WHERE
( [charge_date] = @charge_date_1 AND
[charge_time] = @charge_time_2)
GO
declare @row_id int
declare @JobNumber int
declare @JobName varchar (40)
declare @ChargeDate datetime
declare @ChargeTime datetime
declare @StartTime datetime
declare @EndTime datetime
declare @DepartmentCode varchar (16)
declare @CostCenterCode varchar (16)
declare @EmployeeNumber int
declare @WorkClassification varchar (3)
declare @TaskCode varchar (16)
declare @TaskType int
declare @TaskName varchar(80)
declare @note varchar (254)
declare @Qty decimal (15,2)
declare @UnitName varchar (20)
declare @UnitCost decimal (15,4)
declare @TotalCost decimal (15,2)
declare U_cursor Cursor for select job_number,job_name,charge_date,charge_time,start_time,end_time,department_code,cost_center,employee_number,
work_classification,task_code,task_type,task_name,note,qty,unit_name,unit_cost,total_cost from JM_Updatetbl
open U_cursor
fetch next from u_cursor into @JobNumber,@JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@costCenterCode,@employeeNumber,
@WorkClassification,@TaskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost
While @@Fetch_status <>-1
Begin
fetch next from u_cursor into @JobNumber,@JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@costCenterCode,@employeeNumber,
@WorkClassification,@TaskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost
exec sp_core_get_next_row_id 'job_charges', @row_id output
select @JobNumber=JM_Updatetbl.job_number, @JobName=JM_Updatetbl.job_name,@ChargeDate=JM_Updatetbl.charge_date,@ChargeTime=JM_Updatetbl.charge_time,@StartTime=JM_Updatetbl.start_time,
@EndTime=JM_Updatetbl.end_time,@DepartmentCode=JM_Updatetbl.department_code,@costCenterCode=JM_Updatetbl.Cost_center,
@employeeNumber=JM_Updatetbl.employee_number,@WorkClassification=JM_Updatetbl.work_classification,@TaskCode=tasks.task_code,
@TaskType=tasks.task_type,@TaskName=JM_Updatetbl.task_name,@note=JM_Updatetbl.note,@Qty=JM_Updatetbl.qty,@UnitName=tasks.unit_name,
@UnitCost=tasks.unit_cost,@TotalCost=(job_charges.unit_cost*JM_Updatetbl.qty)
FROM JM_Updatetbl join tasks on JM_Updatetbl.task_name = tasks.task_name,job_charges
insert job_charges (row_id,job_number,job_name,charge_date,charge_time,start_time,end_time,department_code,cost_center_code,employee_number,
work_classification,task_code,task_type,task_name,note,qty,unit_name,unit_cost,total_cost)
values (@row_id,@JobNumber, @JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@CostCenterCode,@EmployeeNumber,
@workClassification,@taskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost)
end
close u_cursor
deallocate u_cursor
drop procedure update_JM_Updatetbl
I have data in a MDB table I need to use to update a SQL table. I have imported it into SQL with A DTS package. So far all is good. I then need to update another SQL table and continue the row_id's that are already in the SQL table.
MDB->SQL[jm_update]-> + [row_id] -> [job_charges]
I created the following code and thought it was working. It works when I am only importing a couple rows at a time but when I import more than 4 or 5 rows it begins making mistakes. It will delete a row and duplicate another. the number of errors grows as the number of rows being imported grows. It always returns the correct number of rows, If I was importing 100 rows it will give me 100 rows, it is just that 5 or 8 will be deleted and or duplicated.
If someone can see what I have wrong or do I need to take a different tack or ???
CREATE PROCEDURE [update_JM_Updatetbl]
( @charge_date_1 [datetime],
@charge_time_2 [datetime],
@job_number_3 [int],
@job_name_4 [nvarchar](40),
@charge_date_5 [datetime],
@charge_time_6 [datetime],
@start_time_7 [datetime],
@end_time_8 [datetime],
@department_code_9 [varchar](50),
@cost_center_10 [varchar](16),
@employee_number_11 [int],
@work_classification_12 [varchar](3),
@task_code_13 [varchar](16),
@task_type_14 [varchar](16),
@task_name_15 [varchar](80),
@note_16 [varchar](254),
@qty_17 [numeric],
@unit_name_18 [varchar](20),
@unit_cost_19 [numeric],
@total_cost_20 [numeric])
AS UPDATE [SMS_1_0].[dbo].[JM_Updatetbl]
SET [job_number] = @job_number_3,
[job_name] = @job_name_4,
[charge_date] = @charge_date_5,
[charge_time] = @charge_time_6,
[start_time] = @start_time_7,
[end_time] = @end_time_8,
[department_code] = @department_code_9,
[cost_center] = @cost_center_10,
[employee_number] = @employee_number_11,
[work_classification] = @work_classification_12,
[task_code] = @task_code_13,
[task_type] = @task_type_14,
[task_name] = @task_name_15,
[note] = @note_16,
[qty] = @qty_17,
[unit_name] = @unit_name_18,
[unit_cost] = @unit_cost_19,
[total_cost] = @total_cost_20
WHERE
( [charge_date] = @charge_date_1 AND
[charge_time] = @charge_time_2)
GO
declare @row_id int
declare @JobNumber int
declare @JobName varchar (40)
declare @ChargeDate datetime
declare @ChargeTime datetime
declare @StartTime datetime
declare @EndTime datetime
declare @DepartmentCode varchar (16)
declare @CostCenterCode varchar (16)
declare @EmployeeNumber int
declare @WorkClassification varchar (3)
declare @TaskCode varchar (16)
declare @TaskType int
declare @TaskName varchar(80)
declare @note varchar (254)
declare @Qty decimal (15,2)
declare @UnitName varchar (20)
declare @UnitCost decimal (15,4)
declare @TotalCost decimal (15,2)
declare U_cursor Cursor for select job_number,job_name,charge_date,charge_time,start_time,end_time,department_code,cost_center,employee_number,
work_classification,task_code,task_type,task_name,note,qty,unit_name,unit_cost,total_cost from JM_Updatetbl
open U_cursor
fetch next from u_cursor into @JobNumber,@JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@costCenterCode,@employeeNumber,
@WorkClassification,@TaskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost
While @@Fetch_status <>-1
Begin
fetch next from u_cursor into @JobNumber,@JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@costCenterCode,@employeeNumber,
@WorkClassification,@TaskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost
exec sp_core_get_next_row_id 'job_charges', @row_id output
select @JobNumber=JM_Updatetbl.job_number, @JobName=JM_Updatetbl.job_name,@ChargeDate=JM_Updatetbl.charge_date,@ChargeTime=JM_Updatetbl.charge_time,@StartTime=JM_Updatetbl.start_time,
@EndTime=JM_Updatetbl.end_time,@DepartmentCode=JM_Updatetbl.department_code,@costCenterCode=JM_Updatetbl.Cost_center,
@employeeNumber=JM_Updatetbl.employee_number,@WorkClassification=JM_Updatetbl.work_classification,@TaskCode=tasks.task_code,
@TaskType=tasks.task_type,@TaskName=JM_Updatetbl.task_name,@note=JM_Updatetbl.note,@Qty=JM_Updatetbl.qty,@UnitName=tasks.unit_name,
@UnitCost=tasks.unit_cost,@TotalCost=(job_charges.unit_cost*JM_Updatetbl.qty)
FROM JM_Updatetbl join tasks on JM_Updatetbl.task_name = tasks.task_name,job_charges
insert job_charges (row_id,job_number,job_name,charge_date,charge_time,start_time,end_time,department_code,cost_center_code,employee_number,
work_classification,task_code,task_type,task_name,note,qty,unit_name,unit_cost,total_cost)
values (@row_id,@JobNumber, @JobName,@ChargeDate,@ChargeTime,@StartTime,@EndTime,@DepartmentCode,@CostCenterCode,@EmployeeNumber,
@workClassification,@taskCode,@TaskType,@TaskName,@note,@Qty,@UnitName,@UnitCost,@TotalCost)
end
close u_cursor
deallocate u_cursor
drop procedure update_JM_Updatetbl