insert/process multi-records without cursor

最新推荐文章于 2025-06-29 22:36:20 发布

转载最新推荐文章于 2025-06-29 22:36:20 发布 · 65 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Winston/archive/2008/01/17/1042650.html

本文介绍了一个SQL存储过程的创建方法，并演示了如何通过该过程将特定的数据格式插入到名为ds_in_item_group_loc_period的表中。此过程涉及从多个表中选择数据并按特定格式组织这些数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

转载于:https://www.cnblogs.com/Winston/archive/2008/01/17/1042650.html