Add GO separator for insert statements in long script

Question

I have a table with a couple of thousand rows. For simplicity I assume it has one column. I need to generate insert statements so I can run that somewhere else.

I use this query:

select 
    'insert into #ids values (' + cast(ID as varchar) + ')' as SqlStr
from MySourceTable

This generates the insert statements but when I run this I get error that cannot run more than 1000 rows in a batch.

Now I'm trying to add GO on every 1000th rows but I don't want to do that manually.

How can I change my query to add GO after every 1000th row?

Answer 1

You can add GO after every 1000th row using below query

;with ct as (
    select 'insert into #ids values (' + cast(ID as varchar) + ')' as SqlStr
        , row_number() over (order by LoanNum) as RN
    from MySourceTable
)
select iif(RN % 1000 = 0, SqlStr + char(10) + 'go' + char(10), SqlStr)
from ct

This simply adds a line-feed to generated statement.

Note that you need to switch the result to TEXT to be able to get the correct output