Bulk Insert & link relationship between inserted row

• LLSQL42

  SSC Enthusiast

  Points: 191

  More actions

  May 21, 2020 at 2:18 am

  #3753472

  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

  More actions

  May 21, 2020 at 2:22 am

  #3753473

  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

  More actions

  May 26, 2020 at 2:36 pm

  #3757445

  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)