Execute dynamic SQL statement Stored in table column

Question

I have a table that stores dynamically built T SQL queries in one of the columns of the table. My requirement is that I need to execute the generated request (in my case, some insertion instructions), and I ' don’t want to use the while loop ' to move through all the lines and then execute the statements from the variable. Also I do not want to use the cursor . The table contains about 5 million dynamically generated SQL insert statements. I used to try this thing using a while loop, but it took several days to complete, and I dropped it.

Answer 1

I had to see what `lakh 'is :)

As noted in other comments, this is not the best approach to DML; consider refactoring. How can you combine your dynamic SQL in packages, for example:

DECLARE @sSQL nvarchar(max) SET @sSQL = 'BEGIN TRAN; ' SELECT @sSQL = @sSQL + COLUMN_WITH_INSERT_STATEMENT + '; ' FROM TABLE WHERE [limit number of rows] SET @sSQL = @sSQL + 'COMMIT TRAN ' EXEC(@sSQL) 

Thus, you can combine a controlled number of INSERT statements in one transaction. You can control the number of inserts using the WHERE statement (for example, WHERE ID BETWEEN 1 and 100 to execute 100 INSERT at a time). You can execute a cycle through this condition (yes, a cycle, but it will not cycle through individual lines, but instead of conditions, instead of 1 - 100, 101 - 200, 201 - 300, etc.).