The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog



No Ads ever, except search!
Tuesday, October 19, 2021 Login

SQL Server Walk the Dog 8/18/2021 6:07:33 AM

Recently I was given several text files with 500k to 1 million update statements in each that needed to be run.

Try loading and running this in SQL Server Management studio and you'll find that if it does load and run, sometimes it fails part through with memory error or other issues (all related to the client, not the engine).

It would have been better for the teams to work together and write this as a table join update as opposed to generating so many individual statements, but that ship had sailed and now these needed to be run.

I ended up "Walking the Dog" or RBAR (Row by agonizing Row).

Load the text files to a table, use a cursor to read each row and dynamically execute it.  I threw in a global counter i could query to figure out where it was at in the process.  In total it took about 2-3 hours to run.

Here is how to walk the dog.



DECLARE @sqlstmt nvarchar(4000)


create table ##Global_Count (current_rownum int)

insert into ##Global_Count values (0)



PRINT '-------- starting --------'; 


DECLARE cur_statements CURSOR FOR  

SELECT sqlstmt FROM DocumentId_3


OPEN cur_statements 


FETCH NEXT FROM cur_statements INTO @sqlstmt




       Exec sp_Executesql @sqlstmt

       update ##Global_Count set current_rownum = current_rownum + 1

FETCH NEXT FROM cur_statements INTO @sqlstmt



Close cur_statements

Deallocate cur_statements


select * from ##Global_Count

drop table ##Global_Count

Blog Home