Professional Web Applications Themes

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

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), ...

  1. #1

    Default 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



    Laphan Guest

  2. #2

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

    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)
    Hugo Kornelis Guest

  3. #3

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

    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" <hugope_NO_rFact.in_SPAM_fo> wrote in message
    news:kmlvl1hd2alrlbl8dcvdu4vhotreq5e4k74ax.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)


    Laphan Guest

  4. #4

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

    >
    > 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
    [url]http://www.upscene.com[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

  5. #5

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

    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


    Gazelem Guest

Similar Threads

  1. Replies: 0
    Last Post: June 1st, 03:15 AM
  2. Saving arabic data back into an Access table
    By gafoorgk in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: March 30th, 10:15 PM
  3. Trouble copying data from old table to new table
    By RelentlessMike in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 19th, 05:01 PM
  4. Replies: 1
    Last Post: July 10th, 08:29 PM
  5. Replies: 1
    Last Post: July 9th, 03:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139