SQL - 基于选定行插入循环

Question

我在sql服务器表中有数千行具有START行，我需要更新表并为每个行INSERT END行。

 select  distinct transaction_id from transactions t1
      where
        this_status_id = 2 and that_type_id = 1
              and not exists
              (
              select  *
              from    transactions t2
              where   t2.transaction_id = t1.transaction_id
                      and t2.this_status_id in (1,3,4)
                      and t2.that_type_id = 1
              )

此选择返回 ID 列表(不是主键(我需要从上面的选择中循环遍历每个 id，并插入到同一个表中，如下所示：

INSERT INTO transactions VALUES (@transaction_id, "finished", 1, 2, GETDATE())

Answer 1

当你可以只循环时，为什么要循环：

insert into transactions
select distinct transaction_id, 'finished', 1, 2, getdate()
from transactions
where this_status_id = 2 
  and that_type_id = 1
  and not exists (
    select  1
    from  transactions t2
    where t2.transaction_id = t1.transaction_id
      and t2.this_status_id in (1,3,4)
      and t2.that_type_id = 1
  );

---

使用临时表：

select distinct transaction_id 
into #TempTable
from transactions t1
where this_status_id = 2 
  and that_type_id = 1
  and not exists (
    select  1
    from  transactions t2
    where t2.transaction_id = t1.transaction_id
      and t2.this_status_id in (1,3,4)
      and t2.that_type_id = 1
  );
insert into transactions
distinct transaction_id, 'finished', 1, 2, getdate()
from #TempTable;