I have 4000 records in a temp table with one column. The column is a customerid that I need to update a record for. As of right now the only thing that comes to mind is running an exec statement for all 4000 records. Is there a more efficient way to do this without the use of a cursor?
Why do it RBAR style? Since all you want is to update a a table based in another one you can just do it by using a join.
update updt set updt.field = 'value' from dbo.TargetUpdatableTable updt join #MyTempTable mtt on mtt.ID = updt.ID
This way you can update your table but only those ids are in the temp table.
If you need to improve the performance of your code it probably makes sense to focus on converting the stored procedure into a set-based operation. For example, instead of issuing 4000 individual
UPDATE queries you could issue one
UPDATE that acts on 4000 rows. Changing from a cursor to some other row by row method benefit is very unlikely to speed up your code.
If you truly need to loop over all of the rows in a table just use a cursor. That's what they're designed for. This is especially true when you're doing with a small number of rows in the table like 4000. The cursor won't take a meaningful amount of time as long as you don't increase the number of rows or have many concurrent processes.
Test procedure and data:
CREATE OR ALTER PROCEDURE dbo.DO_NOTHING (@input_id INT) AS BEGIN DECLARE @i INT; END; GO CREATE TABLE #t (ID INT); INSERT INTO #t SELECT TOP 2000 number FROM master..spt_values UNION ALL SELECT TOP 2000 number FROM master..spt_values;
Here's the code that calls the procedure for each row in the temp table:
DECLARE @id INTEGER; DECLARE @rows_processed INTEGER = 0; DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT id from #t; PRINT 'Start time = ' + CAST(SYSDATETIME() AS VARCHAR(30)); OPEN cur; FETCH NEXT FROM cur INTO @id; WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.DO_NOTHING @id; FETCH NEXT FROM cur INTO @id; SET @rows_processed = @rows_processed + 1; END; CLOSE cur; DEALLOCATE cur; PRINT 'Rows processed = ' + CAST(@rows_processed AS VARCHAR(5)); PRINT 'End time = ' + CAST(SYSDATETIME() AS VARCHAR(30));
The output on my machine:
Start time = 2017-06-02 22:39:23.2709937
Rows processed = 4000
End time = 2017-06-02 22:39:23.3255551
The code ran in 0.05 seconds.