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

Question

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

Answer 1

Do you mean for a test of some kind?

declare @index integer
set @index = 0
while @index < 50000
begin
   insert into table
   values (x,y,z)
   set @index = @index + 1
end

But I expect this is not what you mean.

If you mean the best way to do a bulk insert, use BULK INSERT or something like bcp

Answer 2

Are you inserting from another db/table, programmatically or from a flat file?

Answer 3

From an external data source bcp can be used to import the data. The -b switch allows you to specify a batch size.

Answer 4

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)

begin
declare @n int, @rows int

    select @rows = count(*) from sourcetable

    select @n=0

    while @n < @rows
    begin

     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
    end
end

Answer 5

Could you be more specific on your request? Example of some sort helps

Answer 6

declare @rows as int set @rows = 1 while @rows >0

begin

insert mytable (field1, field2, field3)
select   top 2000 pa.field1, pa.field2, pa.field3 
from table1 pa (nolock) 
left join mytable ta (nolock)on ta.field2 = pa.feild2
 and ta.field3 = pa.field3 and ta.field1 = pa.field1
where ta.field1 is null
order by pa.field1

set @rows = @@rowcount

end

This is code we are currently using in production in SQL Server 2000 with table and fieldnames changed.

Answer 7

With SQL 2000, I'd probably lean on DTS to do this depending on where the data was located. You can specifically tell DTS what to use for a batch commit size. Otherwise, a modified version of the SQL 2005 batch solution would be good. I don't think you can use TOP with a variable in SQL 2000.