SQL - Inserting loop based on select rows

Question

I have thousands of rows in a sql server table which have a START row, I need to update the table and INSERT an END row for each of them.

 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
              )

This select returns a list of IDs (not the primary key) I need to loop through each of the ids from the above select, and INSERT into the same table like:

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

Why reinsert the same ID's? Why don't you perform an UPDATE? — ollie
– ollie, Commented May 23, 2017 at 15:49
Why Loop? INSERT into transactions SELECT distinct transaction_ID, 'finished',1,2,getdate() FROM transactions from MSFT (Insert into select...syntax. Loops are slow... set based processing in a RDBMS is so much more efficient. — xQbert
– xQbert, Commented May 23, 2017 at 15:49

SqlZim · Accepted Answer · 2017-05-23 16:02:44Z

3

Why loop when you could just:

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
  );

Using a temporary table:

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;

edited May 23, 2017 at 16:02

answered May 23, 2017 at 15:50

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3437721 Over a year ago

my select distinct is a crazy long select with not exists etc does that matter since it just brings back a long list of IDs>? I've update my question to show the large SELECT to get the ids

SqlZim Over a year ago

@user3437721 It shouldn't. If you want to dump it into a temporary table first that would be fine as well.

Collectives™ on Stack Overflow

SQL - Inserting loop based on select rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related