Bulk Insert & link relationship between inserted row

Question

Post reply

Bulk Insert & link relationship between inserted row

LLSQL42

SSC Enthusiast

Points: 199
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?

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

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

LLSQL42 SSC Enthusiast Points: 199 More actions · Answer 1

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: 1004617 More actions · Answer 2

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.

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

Bulk Insert & link relationship between inserted row

Cookies on SQLServerCentral