Bulk Insert & link relationship between inserted row

  • LLSQL42

    SSC Enthusiast

    Points: 191

    Hi,

    Let me elaborate with example:-

    I having an existing TableA with below sample column & data

    Date                             Amount                 Category               Name

    2018-01-01                 -100.00                 Debit-Out             Jonathan

    2018-01-01                 -400.00                 Debit-Out             Jonathan

    2018-01-02                 -150.00                 Debit-Out             Jonathan

    2018-01-01                 +100.00                 Credit-In            Jonathan

    2018-01-02                 +400.00                 Credit-In            Jonathan

    2018-01-03                +150.00                 Credit-In            Jonathan

     

    Now, i want move the data to another table, called TableB, with  below features

    1. To add [id] column below, [id] is auto increment during insert

    2. To add [DebitOutFrom] column on last, this is to attach the relationship of debit-out & credit-in, meaning to say, in Credit-In row, i know which row to be Debit Out previously, and only the Credit-In row required to fill up the [DebitOutFrom] column.

     

    My sample data want to achieve as below, i had highlight the value in bold &italic, as below

     

    Id                     Date                             Amount                      Category               Name                      DebitOutFrom

    1                     2018-01-01                 -100.00                 Debit-Out                Jonathan                   NULL

    2                    2018-01-01                 -400.00                 Debit-Out                Jonathan                   NULL

    3                    2018-01-02                 -150.00                 Debit-Out                Jonathan                  NULL

    4                      2018-01-01                 +100.00                 Credit-In               Jonathan                      1

    5                      2018-01-02                 +400.00                 Credit-In              Jonathan                     2

    6                     2018-01-03                +150.00                 Credit-In                Jonathan                    3

     

    Above example,

    The row:4,  the value of DebitOutFrom is 1,    meaning this Credit row, previously is it being  Debit out in row:1

    The row:5,  the value of DebitOutFrom is 2,    meaning this Credit row, previously is it being  Debit out in row:2

    The row:6,  the value of DebitOutFrom is 3,    meaning this Credit row, previously is it being  Debit out in row:3

     

     

    Any suggestion?

    1. Can it be insert first, then perform separate update process the [DebitOutFrom]? OR
    2. During insert, able to achieve that at once?

     

  • LLSQL42

    SSC Enthusiast

    Points: 191

    Hi,

     

    I repaste my text

     

    Hi,

    Let me elaborate with example:-

    I having an existing TableA with below sample column & data

    Date                             Amount                 Category               Name

    2018-01-01                 -100.00                 Debit-Out             Jonathan

    2018-01-01                 -400.00                 Debit-Out             Jonathan

    2018-01-02                 -150.00                 Debit-Out             Jonathan

    2018-01-01                 +100.00                 Credit-In            Jonathan

    2018-01-02                 +400.00                 Credit-In            Jonathan

    2018-01-03                +150.00                 Credit-In            Jonathan

     

    Now, i want move the data to another table, called TableB, with  below features

    To add [id] column below, [id] is auto increment during insert

    2. To add [DebitOutFrom] column on last, this is to attach the relationship of debit-out & credit-in, meaning to say, in Credit-In row, i know which row to be Debit Out previously, and only the Credit-In row required to fill up the [DebitOutFrom] column.

     

    My sample data want to achieve as below, i had highlight the value in bold &italic, as below

     

    Id                     Date                             Amount                      Category               Name         DebitOutFrom

    1                     2018-01-01                 -100.00                 Debit-Out                Jonathan       NULL

    2                    2018-01-01                 -400.00                 Debit-Out                Jonathan       NULL

    3                    2018-01-02                 -150.00                 Debit-Out                Jonathan        NULL

    4                      2018-01-01                 +100.00                 Credit-In               Jonathan          1

    5                      2018-01-02                 +400.00                 Credit-In              Jonathan          2

    6                     2018-01-03                +150.00                 Credit-In                Jonathan        3

     

    Above example,

    The row:4,  the value of DebitOutFrom is 1,    meaning this Credit row, previously is it being  Debit out in row:1

    The row:5,  the value of DebitOutFrom is 2,    meaning this Credit row, previously is it being  Debit out in row:2

    The row:6,  the value of DebitOutFrom is 3,    meaning this Credit row, previously is it being  Debit out in row:3

     

    The value of [DebitOutFrom] , is from  [id] value of Debit-Out row.

    Any suggestion?

    Can it be insert first, then perform separate update process the [DebitOutFrom]? OR

    During insert, able to achieve that at once?

  • Jeff Moden

    SSC Guru

    Points: 996645

    I know this post is a bit old but...

    There's nothing in Table A to absolutely guarantee the correct order of data... that also means there's not going to be anything in table B that you can build your numbering on.  The closest you can come by name,  category, date, and amount.  If that's what you want, then we can come closer.  However, I suspect that the Credit amount won't always exactly match the debit amounts.

    What are you actually trying to do?  Create a running balance or ???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply