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

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

  •  1
  • Prasanna Kumar J  · 技术社区  · 7 年前
    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');
    

    1. 如果 #tableA 然后有一个值

      insert into #tableD  
          select * 
          from #tableA;
      
    2. 如果 #表A 然后是空的

      insert into #tableD  
          select * 
          from #tableB;
      
    3. 如果 #表A #tableB 然后是空的

      insert into #tableD  
          select * 
          from #tableC;
      

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

    5 回复  |  直到 7 年前
        1
  •  1
  •   valex    7 年前

    尝试使用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
    
        2
  •  1
  •   Ab Bennett    7 年前

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

    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 
    
        3
  •  0
  •   dbajtr    7 年前

    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
    
        4
  •  0
  •   Yogesh Sharma    7 年前

    使用(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
    
        5
  •  -1
  •   oɔɯǝɹ    7 年前

    使用此

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