Results 1 to 7 of 7

Thread: Creating Rows Dynamically

  1. #1
    Join Date
    Mar 2003
    Location
    hong kong
    Posts
    8

    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

  2. #2
    Join Date
    Feb 2003
    Posts
    102
    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

  3. #3
    Join Date
    Mar 2003
    Location
    hong kong
    Posts
    8
    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

  4. #4
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201

    Angry

    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'

  5. #5
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    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

  6. #6
    Join Date
    Feb 2003
    Posts
    102
    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

  7. #7
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201

    Smile

    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
  •