Creating Rows Dynamically

Say I have a table containing a list of orders like this

P/O Barcode Ctn/Qty
ADCD 374777579 100
ABCD 374777581 50
DEFG 374777579 200
DEFG 374777581 150

This table says that for each order I have a barcode that will appear to be the same on each of the
100, 50,... cartons. This normal.

What I want to do is for each order and barcode I want to add an extrat unique identity plate.
Therefore if I have 100 cartons on the first line I will create 100 different identity plates, one per carton.

Then my table will become like this

P/O Barcode Ctn/Qty IdPlate
ADCD 374777579 100 0000001
ADCD 374777579 100 0000002
ADCD 374777579 100 0000003
....

How can I code this in SQL? Should I create a stored procedure with the For Each... Next command?
Any idea how to proceed.

Best regards.

Pascal

Pascal,

Most RDBMS's have an Identity or Autonumber field. It is normally an integer that increments by one every time a record is added to the table.

However gaps may appear in this sequence. If you require a solution that does not have any gaps you will have to code your own solution, either using a stored procedure or front end code.

It really does help to answer your post if you mention what RDBMS you are using and what front end (client).

HTH,

Peter

Peter,

Thanks, I was out of town for few days.
Actually I am using Access as a from end.
I guess that the coding should be done in VB in access, isn't it?

Pascal

1.Create table3 (having new column id )
-------------------
create table table3(id int identity(1,1),[P/O] varchar(4),Barcode int,[Ctn/Qty] int)

2.insert into table3 from table1 (distinct)
---------------------
insert into table3 select distinct * from table1

3.create table2 for displaying the result you want
-------------------
create table table2([P/O] varchar(4),Barcode int,[Ctn/Qty] int,IdPlate varchar(7))

4.Write a stored procedure
----------------------
alter proc proc1 as
declare @id int
set @id = 1
while @id <=(select count(*) from table3)
begin

declare @po varchar(4)
declare @ba int
declare @ct int
declare @idp int
declare @string varchar(7)
declare claire cursor for select [p/o],barcode,[ctn/qty] from table1
set @idp = 0
open claire
fetch next from claire into @po,@ba,@ct

while @@fetch_status = 0
begin

-- print @idp
if ((select [p/o] from table3 where id = @id) = @po) and
((select barcode from table3 where id = @id) = @ba) and
((select [ctn/qty] from table3 where id = @id) = @ct)
begin
set @idp= @idp+1
-- PRINT @IDP
if len(@idp) =1
begin
set @string = '000000'+convert(varchar,@idp)
insert into table2 select @po,@ba,@ct,@string
end
if len(@idp) =2
begin
set @string = '00000'+convert(varchar,@idp)
end
if len(@idp) =3
begin
set @string = '0000'+convert(varchar,@idp)
end
if len(@idp) =4
begin
set @string = '000'+convert(varchar,@idp)
end
if len(@idp) =5
begin
set @string = '00'+convert(varchar,@idp)
end
if len(@idp) =6
begin
set @string = '0'+convert(varchar,@idp)
end
if len(@idp) =7
begin
set @string = ''+convert(varchar,@idp)
end
end

fetch next from claire into @po,@ba,@ct
end
close claire
deallocate claire
set @id = @id+1
end

5.Execute the sp
-------------------
exec proc1

6.See the result from table2
---------------------
select * from table2

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
PS.Make sure this is right then only you can go to 7th step.(no suggestion in using step 7th)

7.Rename table table2 as table1(original table)

exec sp_rename 'table2','table1'

can you add insert statement
under every set @string .If you dont add then it will work only from 0000001~0000009

After adding it will work for every case from 0000001 to 9999999

Why use a sproc and a cursor? It seems an unnecessary overhead for generating a number (sorry I don't mean to sound rude, just curious).

--create a new table
CREATE TABLE table2
([P/O] varchar(4),Barcode int,[Ctn/Qty] int ,idplate int identity(1,1))

--insert the records (IDENTITY column will be automatically populated)
-- t1 is your original table
INSERT INTO TABLE2([P/O], Barcode, [ctn/qty])
SELECT t1.[P/O], t1.barcode, t1.[ctn/qty] FROM Table1 as t1

--the select that will bring back you formatted string
--or in Access Format(t2.IDPlate,"0000000") will do the same as the left(etc)
SELECT t2.[P/O], t2.barcode, t2.[ctn/qty],
Left('0000000',Len('0000000') - Len(t2.IDPlate)) + CAST(t2.IDPlate as varchar)
FROM Table2 as t2

SQL Server will manage the sequential number for you, no need for a sproc or cursor.

Use essentially the same technique in if you are using JET (Access) as the RDBMS.

If the users are to see the unique ID and there is to be no gaps in the sequence then maybe an autonumber or identity column is NOT the way to go.

Hope I am not jumping in here as maybe I don't understand the OP's requirments fully.

Peter

peter,
I tried to run your query.But it seems doesnt match what pml's requirement.

--------------------------------------------------------------------------------
I didnt fully understand what plm want.Though he may not see this new update,I still like to share with everybody.

Only today I came to know the real meaning of plm's thread.

Ex.
P/O Barcode Ctn/Qty
ADCD 374777579 100
ABCD 374777581 50
DEFG 374777579 200
DEFG 374777581 150


We need to have a new table which will return something like this

P/O Barcode Ctn/Qty IdPlate
ADCD 374777579 100 0000001
ADCD 374777579 100 0000002
ADCD 374777579 100 0000003
.
.
.
ADCD 374777579 100 0000100
ABCD 374777581 50 0000001
ABCD 374777581 50 0000002
.
.
.
ABCD 374777581 50 0000050
DEFG 374777579 200 0000001
.
.
.
DEFG 374777579 200 0000200

--------------------------------------------------------------------------------
The following is the updated codes:

1.create table table2
CREATE TABLE table2
([P/O] varchar(4),Barcode int,[Ctn/Qty] int ,idplate varchar(20))

2.Using cursor to insert values into table2

declare @po varchar(4)
declare @ba int
declare @ct int
declare @string varchar(500)
declare @id int
declare @idplate varchar(7)

declare cursor1 cursor for select [p/o],Barcode,[Ctn/Qty] from table1
open cursor1
fetch next from cursor1 into @po,@ba,@ct
while @@fetch_status = 0
begin
set @id = 1
while @id <=@ct
begin
set @idplate = replicate('0',7-len(@id))+convert(varchar,@id)
set @string = "insert into table2 select '"+@po+"',"+convert(varchar,@ba)+","+
convert(varchar,@ct)+",'"+convert(varchar,@idplate )+"'"
exec (@string)
set @id = @id+1
end
fetch next from cursor1 into @po,@ba,@ct
end

close cursor1
deallocate cursor1