Hi All
Wonder if you could help, I have a bog standard table called STOCKPRICES
that has served me well for a while, but now I need to change the structure
of it and because a number of users have used it in it's present form I need
to so the following in SQL script:
a) Grab a snapshot of the current SQL data.
b) Re-structure the STOCKPRICES table.
c) Post this grabbed data back, but in the new format.
My script plan was to firstly to rename the current STOCKPRICES table to
STOCKPRICESOLD (you can do this can't you), create a new STOCKPRICES table
in the new format and then somehow extract the data from STOCKPRICESOLD and
squirt it into STOCKPRICES.
The current schema for STOCKPRICES is as follows:
# --------------------------------------------------
# Table structure for table 'STOCKPRICES'
# --------------------------------------------------
DROP TABLE IF EXISTS `STOCKPRICES`;
CREATE TABLE `STOCKPRICES` (
`STOCKID` VARCHAR(30),
`CURRENCYID` VARCHAR(30),
`HDNETAMOUNT` DECIMAL(10,3) DEFAULT 0,
`HDTAXAMOUNT` DECIMAL(10,3) DEFAULT 0,
`RRPNETAMOUNT` DECIMAL(10,3) DEFAULT 0,
`RRPTAXAMOUNT` DECIMAL(10,3) DEFAULT 0,
`NETAMOUNT` DECIMAL(10,3) DEFAULT 0,
`TAXAMOUNT` DECIMAL(10,3) DEFAULT 0,
INDEX `indxCUURENCYID` (`CURRENCYID`),
INDEX `indxSTOCKID` (`STOCKID`)
);
Like I said it's very basic.
My new table wants to be like the following:
# --------------------------------------------------
# Table structure for NEW table 'STOCKPRICES'
# --------------------------------------------------
DROP TABLE IF EXISTS `STOCKPRICES`;
CREATE TABLE `STOCKPRICES` (
`STOCKID` VARCHAR(30),
`CURRENCYID` VARCHAR(30),
`PRICELEVELID` VARCHAR(30),
`NETAMOUNT` DECIMAL(10,3) DEFAULT 0,
`TAXAMOUNT` DECIMAL(10,3) DEFAULT 0,
INDEX `indxPRICELEVELID` (`PRICELEVELID`),
INDEX `indxCUURENCYID` (`CURRENCYID`),
INDEX `indxSTOCKID` (`STOCKID`)
);
The new re-structure means that PRICELEVELID will include a unique reference
to the HD, RRP, standard prices (plus 3 others that I'm going to create).
I know this probably very simple data architecture to you guys, but I'm sure
you can appreciate why I need to change the structure to this method so that
I'm not creating redundant data fields if the user only enters a standard
price I won't be storing nothing for the 2 x HD and 2 x RRP price fields.
I don't think I've got a problem renaming the old one and re-creating the
new one, but how do I get the data from one to another?
My problem is that I have:
code, currency, hdnet, hdtax, rrpnet, rrptax, net, tax
IVP GBP 2.00 0.35 200.00 35.00 100.00 17.50
etc...
and I need to get it into the format:
code, currency, pricelevelid, net, tax
IVP GBP hd 2.00 0.35
IVP GBP rrp 200.00 35.00
IVP GBP standard 100.00 17.50
etc...
Any ideas?
Rgds
Laphan 4 3232
On Wed, 26 Oct 2005 13:23:06 +0100, Laphan wrote:
(snip) I don't think I've got a problem renaming the old one and re-creating the new one, but how do I get the data from one to another?
My problem is that I have:
code, currency, hdnet, hdtax, rrpnet, rrptax, net, tax IVP GBP 2.00 0.35 200.00 35.00 100.00 17.50 etc...
and I need to get it into the format:
code, currency, pricelevelid, net, tax IVP GBP hd 2.00 0.35 IVP GBP rrp 200.00 35.00 IVP GBP standard 100.00 17.50 etc...
Any ideas?
Hi Laphan,
You're crossposting this message to a SQL Server group and a MySQL
group, even though the differences between SQL Server and MySQL are
manifold. Since your CREATE TABLE statements are not valid SQL Server
syntax, I assume that you are actually using MySQL. Why did you include
a SQL Server group as well? Are you considering moving to SQL Server?
The following will get the final step done in SQL Server. I'm not sure
if it works in MySQL as well, but it's fairly standard SQL, so it should
probably work:
INSERT INTO StockPrices (StockID, CurrencyID, PriceLevelID,
NetAmount, TaxAmount)
SELECT StockID, CurrencyID, 'Standard', NetAmount, TaxAmount
FROM StockPricesOld
UNION ALL
SELECT StockID, CurrencyID, 'HD', HDNetAmount, HDTaxAmount
FROM StockPricesOld
UNION ALL
SELECT StockID, CurrencyID, 'RRP', RRPNetAmount, RRPTaxAmount
FROM StockPricesOld
Allow me to offer some advise on your table design as well.
You really need to define a primary key for your tables. Cleaning up
after duplicate data has been inserted is messy.
Also, reconsider your column definitions. Why do you use varchar(30) for
currency code? From the data in your example, I take it that you are
using the three-letter codes defined in ISO 4217 - so why not declare
the column as CHAR(3)? Same for the code - if this is a ticker code, you
don't need three characters. I've never seen ticker symbols longer than
5 characters (though they *might* excist of course - you should know
better than me). And for the PriceLevelID, char(8) or varchar(8) would
do, unless the three extra codes you plan to add have a longer name.
Finally, why are you storing the monetary values as decimal(10,3)? Most
currencies use two decimal places. And if you want to cater for all
currencies, then you'll have to expand to 4 decimal places, since (IIRC)
this is the precision used for some currencies.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Hi Hugo
Many thanks for the detailed response. It is very much appreciated.
I must be honest I am using a MySQL DB, but I thought the SQL theory for
this would be relatively the same. It's just that the SQL server NGs seem
far more helpful and responsive than their MySQL counterparts, as you have
just shown.
Thanks
Laphan
PS: yes, I'm looking to make the currency id and price level id more
realistic as they are codes after all.
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:km********************************@4ax.com...
On Wed, 26 Oct 2005 13:23:06 +0100, Laphan wrote:
(snip) I don't think I've got a problem renaming the old one and re-creating the new one, but how do I get the data from one to another?
My problem is that I have:
code, currency, hdnet, hdtax, rrpnet, rrptax, net, tax IVP GBP 2.00 0.35 200.00 35.00 100.00 17.50 etc...
and I need to get it into the format:
code, currency, pricelevelid, net, tax IVP GBP hd 2.00 0.35 IVP GBP rrp 200.00 35.00 IVP GBP standard 100.00 17.50 etc...
Any ideas?
Hi Laphan,
You're crossposting this message to a SQL Server group and a MySQL
group, even though the differences between SQL Server and MySQL are
manifold. Since your CREATE TABLE statements are not valid SQL Server
syntax, I assume that you are actually using MySQL. Why did you include
a SQL Server group as well? Are you considering moving to SQL Server?
The following will get the final step done in SQL Server. I'm not sure
if it works in MySQL as well, but it's fairly standard SQL, so it should
probably work:
INSERT INTO StockPrices (StockID, CurrencyID, PriceLevelID,
NetAmount, TaxAmount)
SELECT StockID, CurrencyID, 'Standard', NetAmount, TaxAmount
FROM StockPricesOld
UNION ALL
SELECT StockID, CurrencyID, 'HD', HDNetAmount, HDTaxAmount
FROM StockPricesOld
UNION ALL
SELECT StockID, CurrencyID, 'RRP', RRPNetAmount, RRPTaxAmount
FROM StockPricesOld
Allow me to offer some advise on your table design as well.
You really need to define a primary key for your tables. Cleaning up
after duplicate data has been inserted is messy.
Also, reconsider your column definitions. Why do you use varchar(30) for
currency code? From the data in your example, I take it that you are
using the three-letter codes defined in ISO 4217 - so why not declare
the column as CHAR(3)? Same for the code - if this is a ticker code, you
don't need three characters. I've never seen ticker symbols longer than
5 characters (though they *might* excist of course - you should know
better than me). And for the PriceLevelID, char(8) or varchar(8) would
do, unless the three extra codes you plan to add have a longer name.
Finally, why are you storing the monetary values as decimal(10,3)? Most
currencies use two decimal places. And if you want to cater for all
currencies, then you'll have to expand to 4 decimal places, since (IIRC)
this is the precision used for some currencies.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
> Many thanks for the detailed response. It is very much appreciated.
I must be honest I am using a MySQL DB, but I thought the SQL theory for this would be relatively the same. It's just that the SQL server NGs seem far more helpful and responsive than their MySQL counterparts, as you have just shown.
But ... "SQL theory" is something else than Microsoft SQL Server (which is
what the "ms-sqlserver" group is for).
Besides, SQL theory is quite different than the actual SQL implementations
:-)
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions http://www.upscene.com
Database development questions? Check the forum! http://www.databasedevelopmentforum.com
Laphan wrote: It's just that the SQL server NGs seem far more helpful and responsive than their MySQL counterparts, as you have just shown.
That's a pretty blanket statement to make considering this newsgroup
(comp.databases.mysql) is just barely over a month old... You should be
happy getting any reply at all.
Your post is just a hair over 24 hours old as well.
-G This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Leif K-Brooks |
last post by:
I'm writing a relatively simple multi-user public Web application with
Python. It's a rewrite of a similar application which used PHP+MySQL
(not particularly clean code, either). My opinions on...
|
by: D Witherspoon |
last post by:
I am developing a Windows Forms application in VB.NET that will use .NET
remoting to access the data tier classes.
A very simple way I have come up with is by creating typed (.xsd) datasets.
For...
|
by: M Wells |
last post by:
Hi All,
I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'
To explain our situation:
...
|
by: John Smith |
last post by:
..Net 2003 C#
I get xml from a sql database pull.
I put the data into a XMLDomDocument and then the user changes it.
I now need to update the database correctly. I need to be able to get the
data...
|
by: Trishia Rose |
last post by:
this is something ive always wondered, does it take cpu time at run
time to typecast or just at compile time? for example consider the
two little bits of code:
int a = 5;
int b = a;
and:
...
|
by: kai |
last post by:
Hi, All
I create an ASP.NET page, it contains FirstName textbox and LastName
textbox. I setup "enableViewState=false" in page directive. When I enter
data in FirstName and LastName textbox,...
|
by: Ian Davies |
last post by:
Hello
Needing help with a suitable solution.
I have extracted records into a table under three columns 'category',
'comment' and share (the category column also holds the index no of the
record...
|
by: Frank van Wensveen |
last post by:
Friend, coders, fellow wage slaves, lend my your ears.
I believe that in a perfect world the design of a website (or feature
on a website) should be totally separated from its design and the data...
|
by: Troels Arvin |
last post by:
Hello,
Every so often, I'm asked to help people recover data from tables that
were either dropped or where to much data was DELETEed.
The complications related to restoring data are a problem....
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |