|
|
Posted by Hugo Kornelis on 10/26/05 22:47
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)
[Back to original message]
|