-
Mar 2nd, 2014, 02:41 AM
#1
Thread Starter
Fanatic Member
[RESOLVED] How to alter my DB Tables and add the old data into new structure tables
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 into this newly structure tables because this newly structured tables will have more or less columns than the previous one. The client has started working upon the application so how to solve it. Please guide me through...
-
Mar 2nd, 2014, 02:59 AM
#2
Re: How to alter my DB Tables and add the old data into new structure tables
The specifics depend on the specifics. You'll need to execute the appropriate ALTER TABLE statements to add or remove the appropriate columns. New columns will have to accept NULL to begin with because they will be empty. You'll then need to execute appropriate UPDATE statements to populate the new column(s) with appropriate data.
Where and when you do this is up to you. You could build it into the application itself. At startup you could query the database and check the schema and, if it's not the new schema then update it. That means that you'll be testing the schema every time you startup, which is not a big deal but is an overhead. That's probably no good for a multi-user environment where some users may not have appropriate permissions. Alternatively, you could just provide a script and stipulate that it must be run against the database as part of the update process. The app will throw an exception if it's not run but you can only do so much to protect people from themselves.
-
Mar 2nd, 2014, 04:44 AM
#3
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Dearest John, there are so much things i have never faced before... So if you and other precious members are with me so we can reach to the destination.
Please guide me stepwise that from where do i start now, and gradually we'll finish this soon.
my table is SaleMsater and i have to incorporate some fields in it.
Code:
SaleID (Primary Key)
SaleTotalAmount
SalePaidAmount
SaleDiscount
SaleNetBalance
SaleDate
The field I've have to enter for the time being is
Code:
SaleTotalProfit
SaleTotalLoss
-
Mar 2nd, 2014, 05:16 AM
#4
Re: How to alter my DB Tables and add the old data into new structure tables
How about, rather than us guiding you stepwise, you do some appropriate research? I said earlier that you should start with an ALTER TABLE statement. Have you made any effort at all to learn about that or did you just think that you may as well let us do all the work for you?
-
Mar 2nd, 2014, 05:25 AM
#5
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Originally Posted by jmcilhinney
How about, rather than us guiding you stepwise, you do some appropriate research? I said earlier that you should start with an ALTER TABLE statement. Have you made any effort at all to learn about that or did you just think that you may as well let us do all the work for you?
Nah Nah John, you started guiding me and here i am going to start how to work on alter statement.
-
Mar 2nd, 2014, 09:20 PM
#6
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
I found the alter statement and applied it on one my DB as a practice it worked great for me. Now what to do now.
This is original table:
Code:
ContactId
Father_NIC
Contact1
Contact2
I added Contact3 in it, with alter statement
Code:
alter table contact add Contactno3 varchar(15)
It worked for me.
Now what to do JMC next.
-
Mar 2nd, 2014, 09:46 PM
#7
Re: How to alter my DB Tables and add the old data into new structure tables
Originally Posted by ADQUSIT
Now what to do JMC next.
I already told you what to do next in an earlier post so why would I need to tell you again?
-
Mar 3rd, 2014, 06:04 AM
#8
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
My dearest John, everytime you answer me, i feel more comfortable that you are with me.. I feel my track is safe to go forward.... ...
You said that
You'll then need to execute appropriate UPDATE statements to populate the new column(s) with appropriate data.
You want me to execute UPDATE statements because the newly inserted columns will be empty and I have to populate those one will suitable data? right?
I want to explain my application little bit. I developed a standalone PC application, where the actually DB and application both are installed on a single PC right. I installed application and SQL Server 2008 on the client computer and he has started working upon.
-
Mar 3rd, 2014, 09:27 AM
#9
Re: How to alter my DB Tables and add the old data into new structure tables
I'm sure you will work through this, post if you are stuck. One word of advice, er three, backups, backups, backups.
Please remember next time...elections matter!
-
Mar 3rd, 2014, 08:32 PM
#10
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Hello Tyson... Sorry to pick you, but what backups? Do you mean that i take backup of client DB, before altering it?
-
Mar 4th, 2014, 03:30 AM
#11
Re: How to alter my DB Tables and add the old data into new structure tables
Do you mean that i take backup of client DB, before altering it?
Yes. ALWAYS take a back up before altering a production database.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Mar 4th, 2014, 05:11 AM
#12
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Hmm. Well, there is no such option in my app so far to take backup of the application? Should i just copy the .mdf and .ldf files into some safe place?
-
Mar 4th, 2014, 05:49 AM
#13
Re: How to alter my DB Tables and add the old data into new structure tables
You could do that but it's not ideal. Instead you should take a formal backup. The easiest way is to use management studio. You just right click on the database you want to backup and select "backup". It's very easy.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Mar 4th, 2014, 06:37 AM
#14
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Oh.. Yess... Okay. I will follow this.
-
Mar 6th, 2014, 09:50 AM
#15
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
So. Here is my altered table after alter statement.
The table has to be updated now. I tried a simple code, but its very cumbersome way to do it. I cannot make logic for this.
The scenario is that I have a show all button on my form, when i click on it, so all fields of ProductBasicInfo table loads into DGV. When i double click on any product in DGV, so it is loaded into the fields (textboxes) on form and then the user can update the entire entry.
Here is my update code:
Code:
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 & "")
'updateTable("ProductBasicInfo", " ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "'", " ProdName = '" & txtOldValue.Text.Trim & "' and Description = '" & txtOldDesc.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
and this is update function at module level:
Code:
#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
The problem is that as the ProdPrice filed is empty (null) so my update query does not update the records. Only the first entry can be updated because 60 was entered by me manually. Please guide me that how to do this now.
-
Mar 6th, 2014, 10:19 AM
#16
Re: How to alter my DB Tables and add the old data into new structure tables
Wow - you have hardwired SQL statements in code - and now you are altering the table structure.
That can be painful.
If you were using stored procedures life would be so, so much easier.
If you want to limit the pain you could create a new table with same PK as the old table and the new fields. Certainly not something I would typically suggest - but you have to measure the return on investment here.
btw - you should read up on SQL INJECTION - you are way open to attack here.
Also open to just failed inserts when the user puts SQL delimiters in the data!
-
Mar 6th, 2014, 10:32 AM
#17
Re: How to alter my DB Tables and add the old data into new structure tables
I was under the impression this was a one off and will not be done again. Is that not true?
Please remember next time...elections matter!
-
Mar 6th, 2014, 10:42 AM
#18
Re: How to alter my DB Tables and add the old data into new structure tables
Here's some scripts I created recently to alter a table from SSMS
Code:
Use Retfiles
Go
Exec SP_Rename 'Pay1099_T.TDate','Percentage','COLUMN'
Go
Alter Table Pay1099_T Add TDate datetime null
Go
Update Pay1099_T Set TDate=Percentage
Go
Update Pay1099_T Set Percentage=null
Go
Alter Table Pay1099_T Alter Column Percentage int not null
Update Pay1099_T Set Percentage=0
I've got a TDATE field that I like to keep as the last field in every one of my tables (bit OCD of me - so what!)
So that script renamed that field - added a new field - moved the data between the fields and then cleaned up with an UPDATE to set the new field (old TDATE field) to 0
This next example the table had a couple of EXTRA field so that I could rename one and make use of it
Code:
Use Retfiles
Go
select * from penstatus_t
Exec SP_Rename 'PenStatus_T.Extra1','PayGroup','COLUMN'
Go
select * from penstatus_t
Alter Table PenStatus_T Alter Column PayGroup varchar(4)
Go
--Drop Table #XYZ Drop Table #ABC
Select * Into #XYZ From PenMemAct_V
Update PenStatus_T Set PayGroup=(Select Top 1 PayGroup From #XYZ PV Where PV.MasId=PS.MasId and PV.PlanYr<=PS.PlanYr Order by PlanYr Desc)
From PenStatus_T PS
Update PenStatus_T Set PayGroup=(Select Top 1 PayGroup From RetDed_T PC Where PC.MasId=PS.MasId Order by PC.PayPeriod Desc)
From PenStatus_T PS
Where IsNull(PS.PayGroup,'')=''
Select * From PenStatus_T
Where IsNull(PayGroup,'')=''
This next script actually makes a copy of the original table - works it up a bit - and then re-inserts rows from the save table
Code:
Use Acctfiles
Go
Begin Tran
Select * Into Pay_2012_W2W_T From Pay_W2W_T
Delete From Pay_W2W_T
Alter Table Pay_W2W_T Drop Column FILL6
Alter Table Pay_W2W_T Drop Column RW_SEI
Alter Table Pay_W2W_T Drop Column FILL7
Alter Table Pay_W2W_T Drop Column RW_RPI
Alter Table Pay_W2W_T Drop Column RW_TPSPI
Alter Table Pay_W2W_T Drop Column FILL8
Alter Table Pay_W2W_T Add RW_CESHC char(11) null
Alter Table Pay_W2W_T Add FILL6 char(12) null
Alter Table Pay_W2W_T Add RW_SEI char(1) null
Alter Table Pay_W2W_T Add FILL7 char(1) null
Alter Table Pay_W2W_T Add RW_RPI char(1) null
Alter Table Pay_W2W_T Add RW_TPSPI char(1) null
Alter Table Pay_W2W_T Add FILL8 char(23) null
Insert into Pay_W2W_T
Select RW_ORDER, RW_TYPE, RW_SSN, RW_FNAME, RW_MNAME, RW_LNAME, RW_SNAME
, RW_LADDR, RW_DADDR, RW_CITY, RW_STATE, RW_ZIP, RW_ZIPEXT, FILL1
, RW_FSP, RW_FPC, RW_CC, RW_WTOC, RW_FITWH, RW_SSWAGE, RW_SSWH
, RW_MEDWT, RW_MEDWH, RW_SSTIPS, RW_AEIC, RW_DCB, RW_401, RW_403
, RW_408, RW_457, RW_501, RW_MEBQSCP, RW_N457, RW_ECHSA, RW_NN457
, RW_NCP, FILL5, RW_INS, RW_IENSO, RW_DUS409, RW_DRCS401, RW_DRCS403
,'' , FILL6
, RW_SEI, FILL7, RW_RPI, RW_TPSPI, FILL8
From Pay_2012_W2W_T
Select Top 100 * From Pay_W2W_T
Select Count(*) From Pay_W2W_T
Exec SP_Rename 'Pay_W2T_T.FILL3','RT_CESHC','COLUMN'
--Commit--
Rollback
-
Mar 6th, 2014, 11:08 AM
#19
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Originally Posted by TysonLPrice
I was under the impression this was a one off and will not be done again. Is that not true?
You are right Tyson, this won't be done again, but right now, I'm still working on the same (old) project, where i had used the straight sql queries. I"m trying to finish it, but the client everytime asks for somthing new in it.
-
Mar 6th, 2014, 11:27 AM
#20
Re: How to alter my DB Tables and add the old data into new structure tables
Originally Posted by ADQUSIT
You are right Tyson, this won't be done again, but right now, I'm still working on the same (old) project, where i had used the straight sql queries. I"m trying to finish it, but the client everytime asks for somthing new in it.
I thought so and I understand that you need it quick but I suggest you follow szlamany's direction.
How many rows need updated? That could help deciding on a quick and dirty approach.
Please remember next time...elections matter!
-
Mar 6th, 2014, 11:31 AM
#21
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
I'm regret to say that i did not get what is written in your post sir szlamany. It came into my notice after developing this application that straight queries has so many fatal issues, but now I'm trying to put it to end. Please guide me that how to do my problem solved.
-
Mar 6th, 2014, 03:03 PM
#22
Re: How to alter my DB Tables and add the old data into new structure tables
If you are trying to bring this project to completion - then you are not going to stop using inline queries right now.
Just realize that it's a bad idea - start looking at stored procedures. If you do go the SPROC route in the future do not work them into the database directly. Always make a .SQL text file that DROP's and RE-CREATE's SPROCS. Making changes to objects in a database without scripting them is a really bad idea imo (as you see I posted some scripts that changes fields and such)
Is this thread resolved or do you need further assistance?
-
Mar 6th, 2014, 08:58 PM
#23
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
I'll try to use SPROC, as i have never faced with this. For the time, Yes i cannot stop using the incline queries but i have to complete it. And my thread is not resolved yet. My post # 15 is still unanswered and other things are here too, to discuss.
-
Mar 6th, 2014, 09:01 PM
#24
Re: How to alter my DB Tables and add the old data into new structure tables
Originally Posted by ADQUSIT
So. Here is my altered table after alter statement.
The table has to be updated now. I tried a simple code, but its very cumbersome way to do it. I cannot make logic for this.
The scenario is that I have a show all button on my form, when i click on it, so all fields of ProductBasicInfo table loads into DGV. When i double click on any product in DGV, so it is loaded into the fields (textboxes) on form and then the user can update the entire entry.
Here is my update code:
Code:
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 & "")
'updateTable("ProductBasicInfo", " ProdName = '" & txtProdName.Text.Trim & "', Description = '" & txtProdDesc.Text.Trim & "'", " ProdName = '" & txtOldValue.Text.Trim & "' and Description = '" & txtOldDesc.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
and this is update function at module level:
Code:
#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
The problem is that as the ProdPrice filed is empty (null) so my update query does not update the records. Only the first entry can be updated because 60 was entered by me manually. Please guide me that how to do this now.
I have to say I do not understand the problem here. Do you have DGV's that do not know about the new column you added?
Please explain this a whole lot more - thanks!
-
Mar 6th, 2014, 09:19 PM
#25
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
I post # 24 the first image is my DB image in which The ProdPrice has to be updated, this is what i really want. If the records were few so i would update them one by one by there are hundreds of records, so i cannot update them individually. I want that all records all updated at one time.
Yes szlamany, The entire table is being loaded into DGV in the image.
Last edited by ADQUSIT; Mar 7th, 2014 at 05:46 AM.
Reason: Image and further detail added.
-
Mar 7th, 2014, 05:48 AM
#26
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Everytime i double click on any product it gets loaded into fields and then i update the record, but this way is very difficult to do for lots of records. I hope that now the problem will be a bit clearer.
-
Mar 7th, 2014, 06:18 AM
#27
Re: How to alter my DB Tables and add the old data into new structure tables
Originally Posted by ADQUSIT
Everytime i double click on any product it gets loaded into fields and then i update the record, but this way is very difficult to do for lots of records. I hope that now the problem will be a bit clearer.
Figure out what is happening step by step when you do it manually. Post it here and I'll try and help. What you do step by step is what needs done to the table enmass. If it is a case by case decision then it may not be pracitible to do it in a batch. So the first step is what is the decision tree to make the updates.
Sometimes I'll dump the table to a spreadsheet, including the key, and update the spreadsheet, Then I'll update the database with those values.
I just depends on the logic applied to make the updates. Clear as mud?
Please remember next time...elections matter!
-
Mar 7th, 2014, 06:54 AM
#28
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Look Dear Tyson. I have a standalone PC application, where DB and application setup both are residing on same PC. After deployment of my app, the client demanded for some changes, for which I need to alter 3 or 4 tables in my DB. ProductBasicInfo is the first table to be altered, which has the following fields before altering.
Code:
ProdID
ProdName
Description
Manufacturer
Store
AFter this i altered the table and added one more column:
, after which the table has following data.
Now the ProdPrice entire column is empty and i have to update this column with suitable Price against each Product Record.
Now, I have a Product Form with textboxes and a DataGridView. I have a showAll button, by pressing which all the record of the ProductBasicInfo is loaded into DGV. I want to update a single record so i double click on any record in DGV and as a result the record is loaded into Textboxes where the user can update the record. Here is my form img.
In the image i have double clicked on the record and it is loaded into textboxes. The user change the record and press the update button and records gets updated. Here is my update code:
Code:
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")
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
End Sub
UpdateTable is basically a function at module level, given below:
Code:
#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
The problem is that in this way of updating, i can update only a single record at a time. First i will press the showAll button, data will be loaded into DGV, then double click on record to load it into fields and then change the fields and then press the update button. It is a cumbersome and lengthy way for updating hundreds of records, which are actually residing into Client computer. What i want is that isn't there any way by which i can update the entire records in one attempt. Hope this will be little helping in understanding my scenario and problem both. If not so i can give more info.
-
Mar 7th, 2014, 07:33 AM
#29
Re: How to alter my DB Tables and add the old data into new structure tables
Wow - that was tons of information about how your app edits a single record - very nice info.
That info has nothing to do with your goal of updating all the prices in that new column in the table.
"update the entire records in one attempt" is vague. Do you realize that?
Are you saying you want to be able to edit the prices in the grid?
Are you saying you want help with a SQL statement that might load those prices in a single shot?
I have no idea what you are trying to accomplish.
Is it a language issue - English not your first language?
It's been 29 post and 5 days on something that should have been solved in 5 minutes - I'm frustrated - you must be also.
Thing with me - I can leave the thread. You still need a solution.
-
Mar 7th, 2014, 08:16 AM
#30
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Originally Posted by szlamany
That info has nothing to do with your goal of updating all the prices in that new column in the table.
Hm.. what else then you want me to provide you, sir?
"update the entire records in one attempt" is vague. Do you realize that?
Are you saying you want to be able to edit the prices in the grid?
I've never faced this thing before, so i don't know how to do that. But if i am wrong, i might be, then please tell me that what is the right way to do such things. You've also faced this thing before too, and thousands of developers who altering their DB tables, how do they do this. If updating each record is the ultimate solution so i'm happy to do and same is what the client will do too.
I have no idea what you are trying to accomplish.
I've clearly mentioned that i want to update the ProdPrice column, with a single attempt or by one by one (but if it is to be updated one by one so this will take too much time, so therefore, i asked for any easy way, if no way, so i'll be sticky with same individual update way).
Is it a language issue - English not your first language?
This can be a reason too that i cannot provide that information which you all needing.
It's been 29 post and 5 days on something that should have been solved in 5 minutes - I'm frustrated - you must be also.
you are right sir, but learning never frustates me if someone is guiding me.
Thing with me - I can leave the thread. You still need a solution.
Please stay with me until we get it's solution.
Last edited by ADQUSIT; Mar 7th, 2014 at 08:20 AM.
-
Mar 7th, 2014, 12:51 PM
#31
Re: How to alter my DB Tables and add the old data into new structure tables
Originally Posted by szlamany
Are you saying you want to be able to edit the prices in the grid?
Are you saying you want help with a SQL statement that might load those prices in a single shot?
These two questions from my post need to be answered.
Is #1 a YES or NO?
Is #2 a YES or NO?
If #2 is YES then please tell me what TABLE you have that the price is in right now that needs to be used in an UPDATE statement.
-
Mar 7th, 2014, 08:55 PM
#32
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Dear lamany,
1). If this is the solution so yes for # 1 option.
2).
Are you saying you want help with a SQL statement that might load those prices in a single shot?
If i am not inferring your statement wrong so it means that a sql command which inserts the data into the ProdPrice entire field in a single shot then YES is answers. The original prices of the product is in ProductBasicInfo table (which i have altered). ProdPrice is the actual field which has the original prices of all products.
(When i will have all prices in this table then i will use these prices in other forms like sale form and purchase form etc)
-
Mar 7th, 2014, 09:06 PM
#33
Re: How to alter my DB Tables and add the old data into new structure tables
I can give you an update statement to fill the PRODPRICE field in the PRODUCTBASICINFO table - I just need to know what table to GET that value from.
Actually - if I look at this thread you have mentioned many tables and many issues. You should really start a new thread with the "single issue" of getting the PRODPRICE field updated.
-
Mar 8th, 2014, 08:46 PM
#34
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Ok. For solving it i got an idea, i created a new table in which i put the prices (actual prices taken from the client). Here is the table.
Code:
TempID (PK)
ProdID (FK)
PriceList
The price list column has the full prices list.
-
Mar 19th, 2014, 09:04 AM
#35
Thread Starter
Fanatic Member
Re: How to alter my DB Tables and add the old data into new structure tables
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|