Sql – Insert a fixed number of rows 2000 at a time in sql server


I want to insert say 50,000 records into sql server database 2000 at a time. How to accomplish this?

Best Solution

You can use the SELECT TOP clause: in MSSQL 2005 it was extended allowing you to use a variable to specify the number of records (older version allowed only a numeric constant)

You can try something like this: (untested, because I have no access to a MSSQL2005 at the moment)

declare @n int, @rows int

    select @rows = count(*) from sourcetable

    select @n=0

    while @n < @rows

        insert into desttable
        select top 2000 * 
        from sourcetable
        where id_sourcetable not in (select top (@n) id_sourcetable 
                    from sourcetable 
                    order by id_sourcetable)
        order by id_sourcetable

        select @n=@n+2000