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.

7 Years
Discussion Span
Last Post by tesuji


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

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

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

Edited by tesuji: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.