存储过程insert into select

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

GO/****** Object:  StoredProcedure [dbo].[insert_purch_dt_v2]    Script Date: 03/26/2016 19:30:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate procedure [dbo].[insert_purch_dt_v2](@requestId int)asbeginDECLARE @tag intset @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_numwhen 1 then 0end,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=@requestIdinsert 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