Posted by Laphan on 10/26/05 15:23

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

[Back to original message]