-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|