How to take data out of table, restructure the table and then put the data back in

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

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:kmlvl1hd2alrlbl
 8dcvdu4vhotreq5e4k7@
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. databasedevelopmentf
orum.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