June 30, 2004 at 4:59 am
i am planing to do so some structural changes in the tables who already has 10000's of records.
now we have invoice table
invoice_no,invoice_type,invoice_date
and so on are the column names, now there is a column of INVOICE_PAY_RECD1,
INVOICE_Date1,
INVOICE_PAY_RECD2,INVOICE_Date2
and
INVOICE_PAY_RECD3,INVOICE_Date3
suppose if the payment is made against a particular invoice for the first time
invoice_pay_recd1,INVOICE_Date1
column is updated
for the second time
INVOICE_PAY_RECD2,INVOICE_Date2 column is updated.
now what i want is make a seperate table
called
pay_Recd
its structure will be
invoice_no,invoice_type,payrec_date,amount
something like that.
which will be the replacement for above
"is a column of INVOICE_PAY_RECD1,
INVOICE_Date1,
INVOICE_PAY_RECD2,INVOICE_Date2
" data structure which is not very proper
Way
but what to do of already thousands of records already existing.
pls advice me on this.
Thanks in Advance
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
June 30, 2004 at 5:26 am
you've obviously got to keep the data until you have migrated it into the new table.
you should still keep your invoice table with the fields invoice_no,invoice type
then how about running an insert query into the new table (without the invoice type field)
insert into pay_Recd
(invoice_no,payrec_date,amount) select invoice_no,INVOICE_DATE1,INVOICE_PAY_RECD1 from invoice_table where invoice_date1 is not null or invoice_pay_recd1 is not null
go
insert into pay_Recd
invoice_no,payrec_date,amount select invoice_no,INVOICE_DATE2,INVOICE_PAY_RECD2 from invoice_table where invoice_date2 is not null or invoice_pay_recd2 is not null
etc......
wehn these are all migrated simply take a backup and drop the existing fields.
if you want to find out if there is any dependency on the fields that you are going to drop then you can run the following
select sysobjects.name,syscomments.* from sysobjects,syscomments where sysobjects.id=syscomments.id and syscomments.name like '%INVOICE_DATE1%'
MVDBA
June 30, 2004 at 5:29 am
or did you mena that the transaction log becomes fuill because of the amount of data that you are manipulating? if so switch to SIMPLE recovery mode and checkpoint the database after all big update / insert statements/
MVDBA
June 30, 2004 at 5:42 am
Split your tabels then load the old data values first for the base info. Then take the pay and run each item set and populate into the new table for the original. However I question if the invoice_no is unique? If so then consider the following instead.
Invoice Table -- New one
invoice_no, -- PK
invoice_type,
invoice_date
Payment Table
invoice_no, -- FK
payrec_date,
amount
If not then maybe us an IDENTITY column in the Invoice table and use that as you foreign key in the Payment table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply