代码之家 › 专栏 › 技术社区 › Prasanna Kumar J

如果SQL Server 2008 R2中不存在,请插入值

sql-server-2008-r2 sql

Prasanna Kumar J · 技术社区 · 8 年前

create table #tableA(id int, val varchar(50))
create table #tableB(id int, val varchar(50))
create table #tableC(id int, val varchar(50))
create table #tableD(id int, val varchar(50))

insert into #tableB values (1, '11');
insert into #tableB values (2, '22');

如果 #tableA 然后有一个值

insert into #tableD  
    select * 
    from #tableA;

如果 #表A 然后是空的

insert into #tableD  
    select * 
    from #tableB;

如果 #表A 和 #tableB 然后是空的

insert into #tableD  
    select * 
    from #tableC;

我怎样才能用最简单的方法做到这一点?

5 回复 | 直到 8 年前

valex 8 年前

尝试使用Transact-SQL语句 IF..THEN..ELSE

IF EXISTS(SELECT * FROM #TableA) 
BEGIN 
   insert into #tableD  select * from #tableA;       
END
ELSE IF EXISTS(SELECT * FROM #TableB) 
BEGIN
  insert into #tableD  select * from #tableB;
END 
ELSE
BEGIN
  insert into #tableD  select * from #tableC;
END

Ab Bennett 8 年前

第一种方法可以很好地工作,但在处理大量数据时可能会很慢,并且会做比需要更多的工作,但是如果是一个小数据集,它应该可以

insert into #tabled
select * from #tablea union all
select * from #tableb where 0 = (select count(*) from #tableA) union all
select * from #tablec where 0 = (select count(*) 
                                 from (select top 1 id from #tablea 
                                       union all 
                                       select top 1 id from #tableb 
                                       ) x
                                 )

或者第二种方法可以,并且只做必要的工作。

insert into #tableD select * from #tableA
if @@rowcount = 0
begin
   insert into #tableD select * from #tableB
   if @@rowcount = 0
   begin
     insert into #tableD select * from #tableC
     if @@rowcount = 0
     begin
       print('no rows inserted')
     end
     else
     begin
        print('rows inserted from C')
     end     
   end
   else
   begin
     print('inserted from B')
   end
end
else
begin
   print('insert from A')
end

dbajtr 8 年前

if exists (select 1 from #tableA)
    begin
       insert into #tableD
          select * from #tableA
    end
else
    begin
       insert into #tableD
          select * from #tableB
    end

if not exists
    (select 1 from #tableA union select 1 from #tableB)
    begin
       insert into #tableD
          select * from #tableC
    end

Yogesh Sharma 8 年前

使用(Transact-SQL) EXISTS 插入基于条件的值

if exists (select * from #tableA)
begin
    insert into #tableD  select * from #tableA;
end
else
begin
    insert into #tableD  select * from #tableB;
end
if not exists (select * from #tableA)  
begin
       if not exists (select * from #tableB)
       begin
       insert into #tableD  select * from #tableC;
       end
end

-1

oÉÉ¯ÇÉ¹ 8 年前

使用此

INSERT INTO #tabled 
    SELECT * FROM #tableA
UNION ALL 
    SELECT * FROM #tableB
UNION ALL 
    SELECT * FROM #tableC