473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

Oct 26 '05 #1
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)
Oct 26 '05 #2
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)
Oct 26 '05 #3
>
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
Oct 27 '05 #4
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
Oct 27 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
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...
16
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...
2
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: ...
4
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...
8
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: ...
9
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,...
16
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...
10
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...
5
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....
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.