How To insert between Records

Question

How To insert between Records

Hamid-Sadeghian

SSC Eights!

Points: 890
More actions
February 15, 2010 at 11:16 pm

#98541

Hi.I need insert new record between records and update Id.
I writing an accounting system.In this system sometimes user need insert new voucher between vouchers because vouchers must be sort with date and voucher number. User cannot insert into new number and sort with order by.because numbers is not sort.
i write this query but in 200000 record very slow
Update Fs
Set fs.Id=Fs.id+1
From Tbl_Vouchers Fs
Where Fs.id>50
I cannot use float numbering in voucher number. my voucher number must be integer.
Thanks

Viewing 15 posts - 1 through 15 (of 20 total)

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

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

Hi , there is no magic wand to be waved here. Updating 2 million Primary Keys (clustered ?) will take a long time and the fact that you are having to do this does point to a design error.

You really need to update your table design so that it does have a column ( or a composite of columns) that define the report order rather than having to rely on updating of an id.

Clear Sky SQL
My Blog[/url]

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 2

Hi.Thanks.

How to redesign my table.

I have to table for Vouchers. An header table and detail.

in header table i save this data.

------------------

Voucher_Number,Date voucher,User,Type_Voucher

In detail table i save this data.

--------------------

Voucher_Number(Fk of header table),Counter(every row for every voucher.this counter with new voucher number is renumbering from 1).Price,Comment.

I update only the voucher number of header table.in cascading, the detail table is updated.

now, how to redesign my tables?

thanks

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

You have already a date column , if you make that a date/time column does that not serve the same purpose ?

Why is it important to you to have a incrementing sequential voucher number ?

Clear Sky SQL
My Blog[/url]

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 4

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:

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

You may find that dynamically generating the AuditNumber to report on using the ROW_NUMBER() function will be best then.

How is this number used in the real world ?

Is it simply used to number rows on a report or does it carry a meaning ?

Clear Sky SQL
My Blog[/url]

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 6

No.This is a real number.

Because this number is referenced.When Accountant archive voucher this number is reference in the archive.

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

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.

Then that sounds really dangerous as the number is going to change for each row when you insert a new row, making the archive different to the real data.

Clear Sky SQL
My Blog[/url]

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 8

user before archiving can change this number,but after archive the vouchers,cannot change this number.

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

Hamid-Sadeghian (2/16/2010)
user before archiving can change this number,but after archive the vouchers,cannot change this number.

So upto the point of archiving the number is irrelevant , correct ?

Why not allocate an 'ArchiveVoucherNumber' within the archiving process ?

Clear Sky SQL
My Blog[/url]

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

good question Dave.. I don't see any reason for the incremented sequential number for voucher.. the only time that I might have seen that happen was if that a record was being deleted accidentally from the backend, and at the time of deletion they thought that there were no orphan records, but, now somehow the DBA or the programmer wants to fix the issue by re inserting that record back in in case of a possible orphan(s) in other related tables.. make sense?

I have been wrong before.

Cheers,
John Esraelo

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

hamid, salam.. do not translate farsi to english directly.. because you might confuse the readers..

if you want you can write in FarsEnglish and I can translate that for you for your posting or reiterate that here for you.. either way..

but I think I know what you are referring to.

..if I can be any help.

Cheers,
John Esraelo

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

I think in normal accounting, at least in usa, there is a void process that can cancel.. in fact.. any checking account can handle that therefore it should not matter if that record is an account payable or receivable either way there should be a void process as disasters can happen.. meaning a voucher can be eaten by my dog..

🙂

Cheers,
John Esraelo

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

John Esraelo-498130 (2/16/2010)
I think in normal accounting, at least in usa, there is a void process that can cancel.. in fact.. any checking account can handle that therefore it should not matter if that record is an account payable or receivable either way there should be a void process as disasters can happen.. meaning a voucher can be eaten by my dog..
🙂

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.

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

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

John Esraelo-498130 (2/16/2010)
hamid, salam.. do not translate farsi to english directly.. because you might confuse the readers..
if you want you can write in FarsEnglish and I can translate that for you for your posting or reiterate that here for you.. either way..
but I think I know what you are referring to.
..if I can be any help.

It's very good of you to offer help but I thought Hamid was doing pretty well... I've seen much worse.

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