How To insert between Records

Question

How To insert between Records

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Hamid-Sadeghian (2/16/2010)
Because in my country the finance organs need to sequence the voucher number.
for now, i create a temp table and insert all the vouchers into temp table. then select all and sort with date and insert into master table with sequence number. but this way have very problem.for example if system restarted data damaged.
:crying:

Can you do like what programming did in the old days with line numbers? Skip 5 or 10 numbers insert these "odd" rows in between?

--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)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Jeff Moden (2/16/2010)
The void process I'm familiar with in the U.S.A doesn't actually do a "void"... they create a record with the negative value of whatever the transaction was (positive or negative) and insert it as a new record. Part of the reason for this is so that previous and running balances don't have to be recalculated.

About a year ago i got 'overruled' that deleting rows , from a few years previous, from our audit table was a bad idea. I wanted to insert new 'balancing rows'. Guess who had to spend 2 days recovering old backups and proving what happened when our financial auditors discovered a 'discrepancy' with the last few years figures.

<sigh>

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Dave Ballantyne (2/17/2010)
Jeff Moden (2/16/2010)
The void process I'm familiar with in the U.S.A doesn't actually do a "void"... they create a record with the negative value of whatever the transaction was (positive or negative) and insert it as a new record. Part of the reason for this is so that previous and running balances don't have to be recalculated.
About a year ago i got 'overruled' that deleting rows , from a few years previous, from our audit table was a bad idea. I wanted to insert new 'balancing rows'. Guess who had to spend 2 days recovering old backups and proving what happened when our financial auditors discovered a 'discrepancy' with the last few years figures.
<sigh>

Heh... I'm right there with ya on that one. Despite multiple vollies of very high velocity pork chops with white papers, SOX compliance rules, and dozens of posts with warnings that data should never be deleted and that original data must always be protected in an unmodified state, I've been overruled several times. The thing is, I didn't get this old being stupid and, like you, know who's neck will be on the line when they find out they're wrong. So, while they're scrambling for backups that may or may not have been archived a thousand miles away and may or may not actually restore and trying to figure out who they can blame, I sit back and watch for a while. Once I feel they're actually ready to listen (for a change), then I hit the big boss with the fact that I've been archiving the data before they delete it in a hidden database on my desktop or on a special drive somewhere. The big boss normally issues an edict about not ever deleting data shortly after our little talk.

BWAA-HAAA!!! In an odd twist of fate, in order to get people to change, you sometimes have to give them the opportunity to fail. 😛

--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)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Jeff Moden (2/17/2010)
... then I hit the big boss with the fact that I've been archiving the data before they delete it in a hidden database on my desktop or on a special drive somewhere.

Nice idea , but theres not enough storage in the world for all my 'see it told you so' conditions 😀

Clear Sky SQL
My Blog[/url]

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 5

Jeff,

I think, and I could be wrong, if they are not having the "void" system then probably will not leave a hole in the sequence either.

Additionally, the trust and the countability level is very low apparently and therefore having spacers like the old basic / basica / fortran77 / ..etc etc.. style numbering may not be suitable for them..

but that's very good suggestion and I was actually thinking about that too.

well, it looks like that he might be facing some interesting challanges.. I am sure we can have some kind of a text field for audit-trail and/or for any other purpose to keep the sequence in tact..

I am suprised that they are not simply using the IDENTITY col.. just kidding..

Cheers,
John Esraelo

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

Hamid-Sadeghian (2/16/2010)
No.This is a real number.
Because this number is referenced.When Accountant archive voucher this number is reference in the archive.

Voucher number is a business entity, it could just as easily be a series of letters: GHBTRKO, or both: ljhbljhbkkl-5678-kkjnkjnkjn.

If it was a series of letters, you would probably have chosen an integer surrogate pk for the table, because SQL Server maintains it nicely.

You might even argue at the design stage that the integer surrogate key would solve those problems which occasionally arise when a component (a primary key) of the database, which is integral to database structure and function, cannot conveniently model the intricacies of the business.

RowID (pk) = used by the database. Unique, not null, sequence gaps happen and don't matter.

Voucher Number = used by the business. Unique, not null, gaps not good.

The purpose and the definitions are different.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]