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.
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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.