insert/process multi-records without cursor

if object_id('Pr_ToSolver_ds_in_item_group_loc_period') is not null
    drop proc Pr_ToSolver_ds_in_item_group_loc_period


create proc Pr_ToSolver_ds_in_item_group_loc_period
as

insert into  ds_in_item_group_loc_period
    (
    item_group_name
    ,loc_name
    ,bucket_from_name
    ,bucket_to_name
    ,cc
    ,cc_ratio
    ,cc_ratio_over
    ,cc_over
    ,request_fixed_under
    ,request_fixed_over
    ,request_fix
    ,zero_out_build
    )

select
  distinct   ig.ItemGroupName
    ,l.LocationName
    ,ltrim(str(b1.YearCode))+case
                                when b1.WorkWeekCode<=9 then '0'+ltrim(str(b1.WorkWeekCode))
                                when b1.WorkWeekCode>9 then ltrim(str(b1.WorkWeekCode))
                               end
       as bucket_from_name
      ,ltrim(str(b2.YearCode))+case
                                when b2.WorkWeekCode<=9 then '0'+ltrim(str(b2.WorkWeekCode))
                                when b2.WorkWeekCode>9 then ltrim(str(b2.WorkWeekCode))
                               end
       as bucket_to_name
      ,ilp.Capacity
      ,ilp.CapacityRatio
      ,0
    ,0
    ,0
    ,0
    ,0
    ,0
   
from
    ItemGroup_Location_Period ilp
    join ItemGroup ig
        on    ilp.ItemGroupId=ig.ItemGroupId
    join Location l
        on ilp.LocationId=l.LocationId
    left join Bucket b1
        on ilp.BucketId_From =b1.BucketId
    left join Bucket b2
        on ilp.BucketId_To =b2.BucketId
  
   

  

posted on 2008-01-17 11:34  飞天舞者  阅读(216)  评论(0编辑  收藏  举报

导航

For more information about me, feel free email to me winston.he@hotmail.com