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
LaphanIt doesn't look like yur are using Microsoft SQL Server since the DDL in
your post isn't valud Transact-SQL syntax. Below is a SQL Server script for
your problem:
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
);
INSERT INTO STOCKPRICES VALUES(
'IVP', 'GBP', 2.00, 0.35, 200.00, 35.00, 100.00, 17.50)
CREATE INDEX indxCUURENCYID ON STOCKPRICES(CURRENCYID);
CREATE INDEX indxSTOCKID ON STOCKPRICES(STOCKID);
CREATE TABLE STOCKPRICES_New (
STOCKID varchar(30),
CURRENCYID varchar(30),
PRICELEVELID varchar(30),
NETAMOUNT decimal(10,3) DEFAULT 0,
TAXAMOUNT decimal(10,3) DEFAULT 0
);
INSERT INTO STOCKPRICES_New
(
STOCKID,
CURRENCYID,
PRICELEVELID,
NETAMOUNT,
TAXAMOUNT
)
SELECT
STOCKID,
CURRENCYID,
'hd',
HDNETAMOUNT,
HDTAXAMOUNT
FROM STOCKPRICES
UNION ALL
SELECT
STOCKID,
CURRENCYID,
'rrp',
RRPNETAMOUNT,
RRPTAXAMOUNT
FROM STOCKPRICES
UNION ALL
SELECT
STOCKID,
CURRENCYID,
'standard',
NETAMOUNT,
TAXAMOUNT
FROM STOCKPRICES;
DROP TABLE STOCKPRICES;
EXEC sp_rename 'STOCKPRICES_New', 'STOCKPRICES';
CREATE INDEX indxCUURENCYID ON STOCKPRICES(CURRENCYID);
CREATE INDEX indxSTOCKID ON STOCKPRICES(STOCKID);
SELECT * FROM STOCKPRICES;
Hope this helps.
Dan Guzman
SQL Server MVP
"Laphan" <info@.SpamMeNot.com> wrote in message
news:uf6%23Hgi2FHA.3108@.tk2msftngp13.phx.gbl...
> 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
>|||Are you using SQL Server? Certainly looks like something else...

> 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`)
> );
>|||Hi Guys
Many thanks for coming back to me on this.
I must be honest I'm using a MySQL DB, but I thought the SQL theory would be
roughly the same. It's just that this NG is much more helpful and
responsive than the others.
Rgds Laphan
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23%23a2eMj2FHA.1980@.TK2MSFTNGP15.phx.gbl...
Are you using SQL Server? Certainly looks like something else...

> 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`)
> );
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:561637
On Wed, 26 Oct 2005 13:21:52 +0100, Laphan wrote:
(the same as in comp.databases.ms-sqlserver and comp.databases.mysql)
I replied to your -identical- post in comp.databases.ms-sqlserver and
comp.databases.mysql about an hour ago. I now see that this was a waste
of time, since you already had three answers here.
In the future, please:
1. (preferred) post to one on-topic group, no more.
2. (if you really feel you must post to more than one group) use
cross-posting to post to all relevant groups at once. Don't send
seperate copies to more than one group. And do keep the number of groups
as low as possible - two is acceptable, three is a lot, four is almost
allways too much.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)