存储过程insert into select

最新推荐文章于 2025-01-07 15:04:29 发布

朗超

最新推荐文章于 2025-01-07 15:04:29 发布

阅读量3.8k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： sqlserver 文章标签： select

本文链接：https://blog.youkuaiyun.com/JohnLongYuan/article/details/50988466

sqlserver 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个SQL Server存储过程示例，用于将数据从一个表批量转储到另一个表。该过程通过事务确保数据的一致性和完整性。

将程序处理实际会用到的update、insert 事务性语句写成sqlserver存储过程，可以很好的提升执行的效率，
下面是一个实现表数据转储的存储过程


GO

/****** Object:  StoredProcedure [dbo].[insert_purch_dt_v2]    Script Date: 03/26/2016 19:30:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[insert_purch_dt_v2](@requestId int)
as
begin
DECLARE @tag int
set @tag=(select count(b.id)
 from formtable_main_26 a
 left join 
 formtable_main_26_dt1 b on a.id=b.mainid where a.requestId=@requestId and b.id in (select dt_id from formtable_main_31))
if(@tag=0)
 begin
        begin transaction
    insert into formtable_main_31 (requestId,PR_num,submit_date,applicant,apply_dept,
[budget],purch_method,purch_title,purch_type,purch_description,
is_order,is_includecg,
purch_class,purch_num,model_config,forecast_price,forecast_amount,is_assgn,assign_des,choose_supplier,brands,purch_price,purch_amount,
delivery_adress,
delivery_date,hope_comp_date,request_id,formmodeid,modedatacreater,modedatacreatertype,modedatacreatedate,modedatacreatetime,
ordered_num,
orderable_num,
accept_num,dt_id)

 select a.requestId,a.PR_num,a.submit_date,a.applicant,a.apply_dept,
a.budget_xm,a.purch_method,a.purch_title,a.purch_type,a.purch_description,
a.is_order,a.is_includecg,
b.purch_class,b.purch_num,b.model_config,b.forecast_price,b.forecast_amount,b.is_assgn,b.assign_des,b.choose_supplier,b.brands,b.purch_price,b.purch_amount,
b.delivery_adress,
b.delivery_date,b.hope_comp_date,a.requestId,6,89,0,CONVERT(varchar(100), GETDATE(), 23),CONVERT(varchar(100), GETDATE(), 8),
0,
case is_order 
when 0 then b.purch_num
when 1 then 0
end,
0,b.id
 from formtable_main_26 a
 left join 
 formtable_main_26_dt1 b on a.id=b.mainid where a.requestId=@requestId;

 insert into modeDataShare_6_set 
(sourceid,
righttype,sharetype,relatedid,rolelevel,
showlevel,showlevel2,isdefault,layoutid,layoutid1,
layoutorder,isrolelimited,rolefieldtype,rolefield,higherlevel,rightid,requestid)  

select id,
1,5,0,0,
10,null,1,0,0,
0,null,null,null,0,(select MAX(id) from moderightinfo  where modeid=6),@requestId from formtable_main_31  where formmodeid=6 and requestid=@requestId

insert into modeDataShare_6(sourceid,[type],content,seclevel,
showlevel2,sharelevel,srcfrom,opuser,isDefault,layoutid,layoutid1,
layoutorder,higherlevel,setid,rightid,requestid) 
 select 
 sourceid,
sharetype,1,10,
showlevel2,showlevel,5,0,1,0,0,
layoutorder,higherlevel,id,rightid,@requestId from modeDataShare_6_set where requestid=@requestId;
     commit transaction
     end;
end;

GO