MS SQL Server DTS

Author

DTS loop

SQLCat

2006-03-27, 8:27 pm

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

SQLCat

2006-03-28, 9:30 am

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:

> 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

Sponsored Links