/*
我有写个测试数据create table t1(MonthWage datetime,Numbers nvarchar(500))
insert into t1 values('2011-07-26','李四')
create table t2(MonthWage datetime,Numbers nvarchar(500))
--我下面这个语句这样写是可以的.但我不知道这样的语句怎么与加入我下面的存储过程连接
insert Into dbo.t2 select MonthWage, Numbers from dbo.t1 where Numbers not in
(select a.Numbers from t2 as a,dbo.t1 as b where a.MonthWage=b.MonthWage and a.Numbers=b.Numbers)*/我要解决的
insert into t2
(MonthWage,Numbers,KuangGongTianShu)
exec (@ASSelect)
--(@ASSelect)把这个看作t1
存储过程,我把@ASSelect添加到wage表里面
我这里要加个条件当Wage里面有存在MonthWage,Numbers字段与@ASSelect相应的数据则不添加,只添加不存在的这里面
(MonthWage,Numbers,KuangGongTianShu)
exec (@ASSelect)我这三行是把@ASSelect这个结果插入到t2里面.但我要在插入时加个条件.相同的数据不需要添加,有新数据再添加.
create table tb(A int,B varchar(10),C varchar(10),D varchar(10),E int,F int)
go
create procedure getdata
(@flg int)
as
begin
if @flg=1
begin
select 1,'AS','QW','ZX',453,23
union all select 2,'AS','FR','ZX',232,16
union all select 1,'DE','QW','ZX',453,23
union all select 4,'FR','NH','CV',56,78
union all select 4,'DE','HY','CV',789,78
union all select 6,'GT','BG','ZX',5654,89
union all select 4,'JU','NH','CV',56,78
end
end
go
insert into tb(a,b,c,d,e,f) exec getdata 1 --如果存储过程查询结果与表列名一一对应,则不用加列名列表
go
select * from tb
/*
A B C D E F
----------- ---------- ---------- ---------- ----------- -----------
1 AS QW ZX 453 23
2 AS FR ZX 232 16
1 DE QW ZX 453 23
4 FR NH CV 56 78
4 DE HY CV 789 78
6 GT BG ZX 5654 89
4 JU NH CV 56 78(7 行受影响)
*/
drop procedure getdata
go
drop table tb
(MonthWage,Numbers,KuangGongTianShu)
exec (@ASSelect+'as t1 where Numbers not in
(select a.Numbers from t2 as a,dbo.t1 as b where a.MonthWage=b.MonthWage and a.Numbers=b.Numbers)')
如改成这样.说句法错误.
如果是,按你给的代码如下:
insert into t2
(MonthWage,Numbers,KuangGongTianShu)
select MonthWage,Numbers,KuangGongTianShu from @ASSelect as t1
where Numbers not in
(select a.Numbers from t2 as a,dbo.t1 as b where a.MonthWage=b.MonthWage and a.Numbers=b.Numbers)
他不是一个表名,他的值是
set @ASSelect ='
select
MonthWage,DepName,P_Name,#TempTable.PostID,#TempTable.Numbers,#TempTable.Ename,BasePay,
YingGongZuoRi,ShiJiGongZuoRi,Allowance,
'+@GuanLiFei+' as GuanLiFei ,'+@YangLaoJin+' as YangLaoJin,'+@QuanQin+' as QuanQin,PenaltyAmount,ChiDaoZaoTui,#TempTable.EmployeesTypeNumbers,
ShuiDianFei,
BingJia,ShiJia,KuangGongTianShu from #TempTable left join Employees on #TempTable.Numbers=Employees.Numbers'
你可以试下
insert into t2
(MonthWage,Numbers,KuangGongTianShu)
select MonthWage,Numbers,KuangGongTianShu from (
select
MonthWage,DepName,P_Name,#TempTable.PostID,#TempTable.Numbers,#TempTable.Ename,BasePay,
YingGongZuoRi,ShiJiGongZuoRi,Allowance,
@GuanLiFei as GuanLiFei ,@YangLaoJin as YangLaoJin,@QuanQin as QuanQin,PenaltyAmount,ChiDaoZaoTui,#TempTable.EmployeesTypeNumbers,
ShuiDianFei,
BingJia,ShiJia,KuangGongTianShu from #TempTable left join Employees on #TempTable.Numbers=Employees.Numbers
) as t1
where Numbers not in
(select a.Numbers from t2 as a,dbo.t1 as b where a.MonthWage=b.MonthWage and a.Numbers=b.Numbers)
insert into t2
(MonthWage,Numbers,KuangGongTianShu)
exec sp @ASSelect
要把这段insert into t2 (MonthWage,Numbers,KuangGongTianShu)
也要写到那个@ASSelect中去,如下:
*/
set @ASSelect ='
insert into t2 (MonthWage,Numbers,KuangGongTianShu)
select
MonthWage,DepName,P_Name,#TempTable.PostID,#TempTable.Numbers,#TempTable.Ename,BasePay,
YingGongZuoRi,ShiJiGongZuoRi,Allowance,
'+@GuanLiFei+' as GuanLiFei ,'+@YangLaoJin+' as YangLaoJin,'+@QuanQin+' as QuanQin,PenaltyAmount,
ChiDaoZaoTui,#TempTable.EmployeesTypeNumbers,
ShuiDianFei,
BingJia,ShiJia,KuangGongTianShu
from #TempTable left join Employees on #TempTable.Numbers=Employees.Numbers
where Numbers not in
(select a.Numbers from t2 as a,dbo.t1 as b where a.MonthWage=b.MonthWage and a.Numbers=b.Numbers) '