I have a staging db that has one field - col001 - and in it are ~300k INSERT
statements to different dbs. I have the following code that loops through
each line and loads the script accordingly. Problem is, this script takes
forever to complete. Can anyone see if the code could be tweaked to run more
efficiently?

DECLARE @test TABLE ( sql varchar(8000), rowid int identity(1,1))

INSERT @test
SELECT * FROM test where col001 LIKE 'INSERT %'

DECLARE @loop int, @maxloop int, @strSQL varchar(8000)
SELECT @loop = min (rowid), @maxloop = max(rowid) FROM @test

WHILE @loop < = @maxloop
BEGIN

SELECT @strSQL = 'USE Triactive '+Char(13)+sql FROM @test WHERE rowid= @loop

exec ( @strSQL)

SET @loop = @loop + 1
END

actually, if I create an index on the rowid column, the query finishes much
faster. Now I see that, b/c of the length of the data string, there are many
rows that have been "cut off" at the end. I can't change the data type to
text b/c it isn't allowed when claiming a variable - @strSQL text.

Any suggestions?

"SQLCat" wrote: