5 Replies - 658 Views - Last Post: 10 March 2014 - 10:24 PM

#1 Adqusit   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 176
  • Joined: 02-March 13

how to alter my table and incorporate the old data

Posted 02 March 2014 - 03:01 AM

Hi. It is first time that I'm having this problem; I had developed an application which has been deployed on client's computer. Now he requested for some changes in application for which i have to change the tables structure as well as lot of coding too. What i am worry about is that if i make changes to the structure of tables so how i will incorporate the old data (the client has started working upon the application)into this newly structure tables because this newly structured tables will have more or less columns than the previous one. Please guide me that how to solve it.

My table for the time being is:
[u]SaleID[/u]  (Primary Key)
SaleTotalAmount
SalePaidAmount
SaleDiscount
SaleNetBalance
SaleDate


And fields have to incorporate are:

SaleTotalProfit
SaleTotalLoss


Is This A Good Question/Topic? 0
  • +

Replies To: how to alter my table and incorporate the old data

#2 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2334
  • View blog
  • Posts: 7,113
  • Joined: 15-January 14

Re: how to alter my table and incorporate the old data

Posted 03 March 2014 - 12:16 PM

The general answer to that question is that you will need to write a program to do the export from one database, convert the data, and import it into the other one. You'll need to write the rules for how the old data corresponds to the new table structure, and write the code to do that conversion.
Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1733
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: how to alter my table and incorporate the old data

Posted 03 March 2014 - 01:38 PM

Is there a reason your client is designing your database? The only reason I ask is that both of those fields would seem likely to be calculated and displayed from the other data in the row. Or at the very least all the related data that is used to determine what those two fields would hold. Another concern is that you would seem to only fill one or the other of those two as you wouldn't seemingly have a profit and a loss on the same information involved in getting a result. Even a single field that holds a positive or negative value would seem more efficient.

Then it's just a matter of having a gui in place to display those values.

Here's why I bring this up.

You will now have multiple areas where the information is stored that you would calculate against to get the ProfitTotal or LossTotal. So if you have something change in one of those bits of information, you would then need to make sure you also update one of these fields to show the new Total.

Now with that logic, since you would need to incorporate this into your code, wouldn't it be easier to just add the two fields, write a routine that gathers the information to get this result and then update the row in one fell swoop? Because that's what you are apparently going to be doing anyway if you decide to pull the data and save it into your new table structure.
Was This Post Helpful? 0
  • +
  • -

#4 Adqusit   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 176
  • Joined: 02-March 13

Re: how to alter my table and incorporate the old data

Posted 08 March 2014 - 08:31 PM

Let me have a simple example.
I have a table:
ProductBasicInfo

ProdID  (PK)
ProdName
Description
Manufacturer
Store


After alter statement i added a new column in it, The new field is:

ProdPrice



As this new field is entirely empty against all records, so i need to update this column with appropriate data. Please guide me for this.

This post has been edited by Adqusit: 08 March 2014 - 08:32 PM

Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7447
  • View blog
  • Posts: 15,441
  • Joined: 16-October 07

Re: how to alter my table and incorporate the old data

Posted 09 March 2014 - 02:59 AM

I'm sorry, you just answered your own question?

View PostAdqusit, on 08 March 2014 - 10:31 PM, said:

After alter statement i added a new column in it, The new field is:

ProdPrice



As this new field is entirely empty against all records


Yep, the field is empty. Only you can say what data should be placed in that field; it's your data!

You may add the field allowing NULL, update the field on all rows to 0, then add a NULL constraint, if that is the question.
Was This Post Helpful? 0
  • +
  • -

#6 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: how to alter my table and incorporate the old data

Posted 10 March 2014 - 10:24 PM

how do you want to update it? what is your requirement to update that null column or just follow baavgai comments
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1