把一个表数据连同其他数据插入另一个表中

create proc p_test( @dt datetime, @ret int output ) as



declare @cnt int

select @cnt = count(*) from kqjl where datediff(m,rq,@dt) = 0

if @cnt > 0

begin

	set @ret = 1

	return

end



insert into kqjl(bh,xm,bm1,bm2,bm3,zglx,bm,rq,k01) 

(select bh,xm,bm1,bm2,bm3,zglx, 

((select rtrim(c.bmmc) from bm1 c where c.bm1 = a.bm1) + ( case when ( select rtrim(d.bmmcs) from bm2 d where d.bm2 = a.bm2 ) is null then '' else '/' + ( select rtrim(d.bmmcs) from bm2 d where d.bm2 = a.bm2 ) end ) + ( case when ( select rtrim(e.bmmcss) from bm3 e where e.bm3 = a.bm3 ) is null then '' else '/' + ( select rtrim(e.bmmcss) from bm3 e where e.bm3 = a.bm3 ) end ) ) as bm, 

@dt,

CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,getdate())+1,0)-DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)AS INT ) as ts 

from ygml a)



set @ret = 0

//代码

datetime  ldt_rq

int		 li_ret



em_1.getdata(ldt_rq)



DECLARE P1 PROCEDURE FOR P_test

   @dt = :ldt_rq,

   @ret = :li_ret OutPut  

    ;

EXECUTE P1;

IF sqlca.sqlcode <> 0 THEN

	Messagebox( '', '执行失败' )

	RollBack;

ELSE

	FETCH P1 INTO :li_ret ;

	Commit;	

END IF



if li_ret > 0 then

	messagebox( '','本月记录已经存在' )

	return

end if



//刷新dw

declare @a table ( bm1 int, bm2 int, bm3 int )

declare @c table( bh1 int, mc varchar(10) )

declare @d table( bh2 int, mc varchar(10) )

declare @e table( bh3 int, mc varchar(10) )



insert @a select 1,2,3

insert @a select 1,2,null

insert @a select 1,null,null

insert @a select null,2,3



insert @c select 1, '一'

insert @d select 2, '二'

insert @e select 3, '三'



select bm1,bm2,bm3,( (select c.mc from @c c where c.bh1 = a.bm1) + ( case when ( select d.mc from @d d where d.bh2 = a.bm2 ) is null then '' else '/' + ( select d.mc from @d d where d.bh2 = a.bm2 ) end ) + ( case when ( select e.mc from @e e where e.bh3 = a.bm3 ) is null then '' else '/' + ( select e.mc from @e e where e.bh3 = a.bm3 ) end ) ) as bm from @a a





/*结果

bm1         bm2         bm3         bm                               

----------- ----------- ----------- -------------------------------- 

1           2           3           一/二/三

1           2           NULL        一/二

1           NULL        NULL        一

NULL        2           3           NULL

*/