IF EXISTS(select e,f,g from B where e!=15 )
BEGIN
insert into A
select e,f,g from B where e!=15
union all
select C.e,C.f,C.g from C
END
BEGIN
insert into A
select e,f,g from B where e!=15
union all
select C.e,C.f,C.g from C
END
你的先执行了insert into 后判断条件
容易出现问题,不严密。楼上说的除e中存在null的
BEGIN
insert into A
select e,f,g from B where e!=15 OR E IS NULL
union all
select C.e,C.f,C.g from C
END
BEGIN
insert into A
select e,f,g from B where e<>15
union all
select e,f,g from C where e<>15
END
insert @B
SELECT 15,'a','t' UNION ALL
SELECT 12,'a','r' UNION ALL
SELECT 11,'d','c'DECLARE @C TABLE(e int,f nvarchar(10),g nvarchar(10))
insert @C
SELECT 15,'w','b' UNION ALL
SELECT 13,'g','j' UNION ALL
SELECT 10,'a','h'IF EXISTS(select 1 from @B where e<>15 )
BEGIN
insert into @A
select e,f,g from @B where e<>15
union all
select e,f,g from @C where e<>15
ENDselect * from @Ae f g
----------- ---------- ----------
12 a r
11 d c
13 g j
10 a h(4 行受影响)
insert @B
SELECT 15,'a','t' UNION ALL
SELECT 15,'a','r' UNION ALL
SELECT 15,'d','c'DECLARE @C TABLE(e int,f nvarchar(10),g nvarchar(10))
insert @C
SELECT 15,'w','b' UNION ALL
SELECT 13,'g','j' UNION ALL
SELECT 10,'a','h'IF EXISTS(select 1 from @B where e<>15 )
BEGIN
insert into @A
select e,f,g from @B where e<>15
union all
select e,f,g from @C where e<>15
ENDselect * from @Ae f g
----------- ---------- ----------(0 行受影响)
IS NULL 不是表示为空吗,表B中e若为空,那就不向A表中插入B和C表的数据了。
意思就是 b表中的e要么不等于15 要么不为NULL 这2种情况都不插入
BEGIN
insert into A
select e,f,g from B where e!=15 -- OR E IS NULL 这句不需要的,因为既然e is null 的就不会插入了 因为前面已经判断了。
union all
select C.e,C.f,C.g from C
END
其实
insert into A
select C.e,C.f,C.g from C where exists(select e,f,g from B where e!=15)
union all
select e,f,g from B where e!=15