CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Dibin
Dibin

Reputation: 1578

Execute A Dynamic SQL statement Stored in a Column of a table

I have a table that stores dynamically constructed T SQL queries in one of the table's column. My requirement is that I need to execute the query formed (in my case, some insert statements) and I 'dont want to use a while loop' to traverse the whole rows and then executing the statements from a variable. Also I dont want to use a cursor. The table has around 5 million of dynamically formed SQL insert statements. I had earlier tried this thing using a while loop but it was taking days to complete, and so I dropped it.

Upvotes: 6

Views: 14074

Answers (1)

suff trek
suff trek

Reputation: 39777

Had to look up what `lakh' was :)

As other comments mentioned this is not most optimal approach to DML, please consider refactoring. As it is you can combine your dynamic SQL in batches, e.g.:

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)

This way you can combine controlled number of INSERT statements into a single transaction. You can control number of inserts by means of WHERE statement (e.g. WHERE ID BETWEEN 1 and 100 to execute 100 INSERTs at a time) You can loop thru this condition (yes loop, but it it will not be looping thru individual rows, but thru conditions instead e.g.1 - 100, 101 - 200, 201 - 300 etc).

Upvotes: 4

Related Questions