Inserting repeating incremental values

[ssipiora]

I"m very new to SQL but need to insert LOT numbers to inventory.

I have my table (cards) and my column (lot).

The cards come in lots of 25 units. Each lot increments by 1. So rows 1-25 would have lot 0001, 26-50 would have a lot of 0002, etc. I need to do this for 1,000,000 rows.

I've looked at a lot of resources and books and can't find any examples of how to do this.

Any ideas?

Thanks in advance.

[tesuji]

Hello

I would do that with a user defined function or with any other .net programming language or even Java. Do you know Transact SQL, the SQL Server´s programming language which is very similar to standardized PSM language?

Below user defined function, written transact-like, should insert @nbofRows in column lotnumber of yourTable where every 25 units the lot number is increased by 1.

create function owner.lotsCards(@nbofRows int) returns int
as
begin
  declare @lot int;
  declare @lotsNumber int;
  declare @lotsUnit int;
  declare @nrow int;
  set @lotsUnit  = 25;                      -- The cards come in lots of 25 units.
  set @lotsNumber = @nbofRows / @lotsUnit;  -- the number of lots, 1000000/25=40000 lots
  message 'Number of lots: ' || @lotsNumber to client;            
  set @lot = 1;                  
  while @lot <= @lotsNumber loop            --  loop on all lots 
    set @nrow = 1;               
    while @nrow <= @lotsUnit loop           --  inserting 25 times same lot number, e.g. 0001
      /*** this insert statement should be adapt to your table and column ***/
      insert into owner.yourTable (lotnumber /*are there any other column too ? */) values (@lot);
      set @nrow = @nrow + 1;    
    end loop;
    commit;               -- every 25 inserts a commit is done. Important: if this function 
                          -- runs within larger tanscation, this commit MUST BE DROPPED!
    set @lot = @lot + 1;
  end loop;
  return @lotsNumber;
end;

You should change qualifier owner. and the create-statement to yours. This code hasn't been tested so there might by typos or even logical errors, pls check carefully.

How to use function owner.lotsCards?
Being on a console (e.g. sql server management studio), copy above code to it and type GO. Then call the function from a select statement. You can also create a variable xx and set xx = owner.lotsCards(1000).
--
-- copy function here
--
GO

select owner.lotsCards(1000000) as "The highest lot number" ;
-- (I would first test it with row numbers 1000 or so)

Inserting 1,000,000 rows with only one column and nothing else should last approx. one minute.

-- tesu