asked
Run an Automatically Generated UPDATE Statement
I created a query that automatically generates update statements So the output of the query would return results such as below (this is a simplified output usually it produces hundreds of update statements: ID UPDATEScript 123 UPDATE TestTbl set Name = 'Smith' where ID = '123' 456 UPDATE TestTbl set Name = 'Jones' where ID = '456' 789 UPDATE TestTbl set Name = 'Bob' where ID = '789' What I usually do after running this query is copy all the UPDATE statements that get generated and run them in a new query window in SSMS. What I want to try to do and I do not know if this is possible is to have an Update query that runs all the update statements automatically so I do not need to copy and paste all the statements? I was thinking of storing the update statements that get produced in a temp table and then trying to run an update based on the Script that would be in the temp_tbl UpdateScript Column but not sure of the correct Syntax to get it to run the UpdateStatement that is in the TempTable: This is the idea although I know this query won’t work UPDATE testTbl ( Select UpdateScript FROM #tempTbl) Any suggestions would be great, Thanks!
Dynamic SQL - build the query as a string and then EXECute it.
http://msdn.microsoft.com/en-us/library/ms188332.aspx DECLARE @MySQLQuery varchar(max) SELECT @MySQLQuery = '' SELECT @MySQLQuery = @MySQLQuery + UPDATEScript FROM #tempTbl EXEC (@MySQLQuery)
9 People are following this question.
