SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to alter Table and Add the old Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 03/03/2014 :  06:27:28  Show Profile  Reply with Quote
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:

SaleID  (Primary Key)
SaleTotalAmount
SalePaidAmount
SaleDiscount
SaleNetBalance
SaleDate


And fields have to incorporate are:


SaleTotalProfit
SaleTotalLoss


I ran the alter query and it worked fine for me. But the problem is that If i give this new DB to client, so how i will incorporate the old data into this newly altered DB?

Let me briefly explain my application. I developed a standalone PC application where both DB and application are installed on a single PC.

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 03/03/2014 :  06:53:32  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Simple way is, prepare an Alter Script (Adding new column) and execute in client DB to change the table structure.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/03/2014 :  13:03:07  Show Profile  Reply with Quote
quote:
Originally posted by Abid

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:

SaleID  (Primary Key)
SaleTotalAmount
SalePaidAmount
SaleDiscount
SaleNetBalance
SaleDate


And fields have to incorporate are:


SaleTotalProfit
SaleTotalLoss


I ran the alter query and it worked fine for me. But the problem is that If i give this new DB to client, so how i will incorporate the old data into this newly altered DB?

Let me briefly explain my application. I developed a standalone PC application where both DB and application are installed on a single PC.


If new tables have more columns its not an issue. The existing columns will have data preserved whereas new columns will only have data for subsequent rows
If its opposite ie table having less columns, then if any of dropped columns are used in clients application, application will break after this change. So you need to check dependency before removing exusting columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/03/2014 :  13:04:36  Show Profile  Reply with Quote
Since you have altered the old table, now the old table contains these two new columns which are null/empty at the moment.
Run the UPDATE query for both new columns and you are good to go.


!_(M)_!
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 03/06/2014 :  11:43:51  Show Profile  Reply with Quote
@Visakh, Yes, i have to add columns, not to remove. I've added 1 column below, with alter statement. Column is added successfully, but you know that this will be empty and new records will be fully inserted with data. But the problem is that how to update the newly entered empty Column with suitable data. The older table was:

ProductBasicInfo

ProdId (Primary Key)
ProdName
Description
Manufacturer
Store

The newly inserted field is
ProdPrice,

which is currently null for all the records. Surely the new records will be properly inserted but i have to fill the empty records. Please guide me now that how to update the empty records. Is my problem understand to you sir?

@maunishq
How to run the suitable update command.
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 03/06/2014 :  11:45:23  Show Profile  Reply with Quote
My update query is:

Try
            updateTable("ProductBasicInfo", "ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "', Manufacturer = '" & txtProdManuf.Text.Trim & "', Store = " & txtStore.Text.Trim & ", ProdPrice = " & txtProdPrice.Text.Trim & "", " ProdName = '" & txtOldValue.Text.Trim & "' and Description = '" & txtOldDesc.Text.Trim & "' and Manufacturer = '" & txtOldManuf.Text.Trim & "' and Store = " & txtOldStore.Text.Trim & " and ProdPrice = " & txtOldProdPrice.Text.Trim & "")
            
            MessageBox.Show("Record updated successfully")

            btnUpdate.Enabled = False
            btnProdSubmit.Enabled = Enabled

            txtProdName.Clear()
            txtProdDesc.Clear()
            txtProdManuf.Clear()
            txtStore.Clear()
            txtProdPrice.Clear()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cnSql.Close()
        End Try


Where updateTable is a function in a separate module:

#Region "Updating"

    Public Sub updateTable(ByVal tblName As String, ByVal parameter As String, ByVal criteria As String)

        Try
            cmdSql1.CommandText = " update " & tblName & " set " & parameter & " where " & criteria & ""
            cmdSql1.Connection = Connect()
            daSql.UpdateCommand = cmdSql1
            daSql.Fill(dsSql, tblName)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
#End Region

Edited by - Abid on 03/06/2014 11:46:14
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/06/2014 :  12:21:46  Show Profile  Reply with Quote
Not familiar with the code you've provided :)

So the issue is to update the new field with appropriate data, since by the time you alter it it won't be having any information for the records already exists. You may use two approaches

1) explicitly update: after you add these fields and do appropriate coding in the application to populate it. It would turn out to be scenario that for all new records the information would be available, except for the ones that were added before these new fields. So provide the old data to the user/client and request them to fill it out for you with their desired values. once they provide it update the new fields accordingly via SQL.

2) Add as Computed fields: the new fields sounds more like computed information. Considering that you know the pre-req of a computed column, and with that, iff this is the case and all the fields are available on that table to fulfill the cumputation, add them as computed column.



Cheers
MIK
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/06/2014 :  14:32:36  Show Profile  Reply with Quote
So, You want to update SaleTotalProfit and SaleTotalLoss columns of your newly altered table.
UPDATE new_upd_tbl
SET SaleTotalProfit = ...calculated formula...,
SaleTotalLoss = ...calculated formula...

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 03/07/2014 :  08:17:07  Show Profile  Reply with Quote
@ MIK, explicit updating, means that i update each and ever record one by one, is it what you want me to do?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/07/2014 :  09:28:46  Show Profile  Reply with Quote
that's not what I want you to do.. but if the requirements are such that you need to go through that approach then you've to do it that way .. :)

If there are thousands of record already exist in the table, how come you know what should be the profit and loss for each record, unless there is a formula which can be used to derive this information, so you need to get this information from client. Generally in such cases an excel sheet is provided where the client/requester fill it up with the desired information and give it back to the IT programmer / DBA/ Developer. Who then use that information and update the corresponding records. This update could be using one by one UPDATE Table (DML) commands or by and advance way like reading the excel file using openrowset etc functions, and joining it back to the extent table using the PK information and simply updating the two new fields.


On the other hand if there is a generic rule for computing profit and loss information then you can use computed field e.g.

We have a table that has the following fields and are already having huge amount of data.
Product, OriginalPrice, Price_Sold

Now you need to add two new fields, Profit and Loss to this table. And the concept behind Profit is: if Price_Sold - OriginalPrice is "positive number" that means its a profit and if the same turns out to be "negative number" means its a loss. So you can add them as computed field to the table.

Its all about the approach (more precisely "correct" approach) so solve the issue.

Cheers
MIK
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 03/07/2014 :  11:24:44  Show Profile  Reply with Quote
Let's have a simple one table, of the same project, before solving this complex one of Sale.

Hi. I have a table called ProductInfo having the fields:

ProdID (Primary Key)
ProdName
Description
Manufacturer
Store

later on, on client demand i altered the table and added a new column called:
ProductPrice.


As the new column in table is entirely empty (null), so i need to update it with
suitable values (price) against each product record in a table.

My code for updating a record is:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Try

updateTable("ProductBasicInfo", "ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "', Manufacturer = '" & txtProdManuf.Text.Trim & "', Store = " & txtStore.Text.Trim & ", ProdPrice = " & txtProdPrice.Text.Trim & "", " ProdID = " & txtProdID.Text.Trim & "")

MessageBox.Show("Record updated successfully")
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnSql.Close()
End Try
End Sub


UpdateTable is actually a function at module level in vb.net module.

This query only updates individual record at a time, but in actual scenario i have hundreds of records to be updated, So Isn't there any way by which i update all records with single attempt.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/07/2014 :  12:11:10  Show Profile  Reply with Quote
if you want how to do it via VB code I dont know .. neither this forum is for VB coding.

But if you want to update via SQL, I think I've already given you the answer for this. For hundreds of records you need to have hundreds of values for each record of the ProductInfo table. Once you have this information. Create a temporary table (ProdID, Price) and load it with required information and then use following query to get the ProductInfo table updated in one go

UPDATE A
SET Price=tempTable.Price
FROM ProductInfo A
INNER JOIN tempTable on tempTable.ProdID=A.ProdID

Cheers
MIK

Edited by - MIK_2008 on 03/07/2014 12:14:58
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 03/07/2014 :  21:00:34  Show Profile  Reply with Quote
Dear MIK, let me clear my self. You want me that the client will give me all the prices (list/ information) and i will put it in a separate table (e.g. PriceTable) and then i will update my ProductInfo table with that newly created table? Did I pick you right sir?
Go to Top of Page

Abid
Posting Yak Master

Pakistan
109 Posts

Posted - 03/08/2014 :  22:47:57  Show Profile  Reply with Quote
quote:
Originally posted by MIK_2008

if you want how to do it via VB code I dont know .. neither this forum is for VB coding.

But if you want to update via SQL, I think I've already given you the answer for this. For hundreds of records you need to have hundreds of values for each record of the ProductInfo table. Once you have this information. Create a temporary table (ProdID, Price) and load it with required information and then use following query to get the ProductInfo table updated in one go

UPDATE A
SET Price=tempTable.Price
FROM ProductInfo A
INNER JOIN tempTable on tempTable.ProdID=A.ProdID

Cheers
MIK



What is A MIK, in the query?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000