dcsimg
Results 1 to 2 of 2

Thread: Need to create a record into speard and need to update the data accordingly

  1. #1
    Join Date
    Dec 2015
    Posts
    1

    Need to create a record into speard and need to update the data accordingly

    I have table A, which contains code and range of value as below :

    Scenario 1 :
    Table A
    Code1 Code2 HighRange LowRange
    001 002 0000 9999

    I have to insert the record in table "B" with above information.

    Scenario 2 :
    Table A
    Code1 Code2 HighRange LowRange Block
    001 002 0000 9999 A ---> This is common block will have in table "A" for each combination of code.
    001 002 6000 6999 6

    In this scenario, I have to insert in "B" as below :

    Table B
    Code1 Code2 HighRange LowRange
    001 002 0000 5999
    001 002 6000 6999
    001 002 7000 9999

    Scenario 3 : say I get a combination of 8000 -8999
    Table A
    Code1 Code2 HighRange LowRange Block
    001 002 0000 9999 A ---> This is common block will have in table "A" for each combination of code.
    001 002 6000 6999 6
    001 002 8000 8999 8

    In this scenario, I have to insert in "B" as below :

    Table B
    Code1 Code2 HighRange LowRange
    001 002 0000 5999
    001 002 6000 6999
    001 002 7000 7999
    001 002 8000 8999
    001 002 9000 9999

    Scenario 5 : say I get a combination of 2000 to 2999
    Table A
    Code1 Code2 HighRange LowRange Block
    001 002 0000 9999 A ---> This is common block will have in table "A" for each combination of code.
    001 002 6000 6999 6
    001 002 8000 8999 8
    001 002 2000 2999 2

    In this scenario, I have to insert in "B" as below :

    Table B
    Code1 Code2 HighRange LowRange
    001 002 0000 1999
    001 002 2000 2999
    001 002 3000 5999
    001 002 6000 6999
    001 002 7000 7999
    001 002 8000 8999
    001 002 9000 9999

    Any help is much appreciated. Block id will just tell which block have a changes, example if its 000 to 999 then it will be "0", if it 1000 to 1999 then it will be "1", like that it will go till "9".

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,910
    It can't be done using straight forward SELECT and INSERTS. You will have to write a procedure with a cursor to go through each row of table A ordering them by code1, code2 and block, then insert multiple rows into table B.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •