Professional Web Applications Themes

Moving data from one table to another, (existing), one. - MySQL

Hi, I have a table, TABLEA ( ROWA[int], ROWB[int] ) and I want to move all the data to another table, TABLEB ( ROWA[int], ROWB[string], ROWC[string] ) ROWC will be a constant string but ROWB will be converted to a string (VARCHAR 255) of the current INT value. How would you move the data from A to B? INSERT INTO TABLEB ( ROWA, ROWB, ROWC ) VALUES ( SELECT ROWA, ROWB FROM TABLEA, 'ROWC'.... ) Not sure how that would work. Thanks FFMG -- 'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory' (http://www.webhostshunter.com/) | 'Recreation Vehicle insurance' (http://www.insurance-owl.com/other/car_rec.php) 'Free URL redirection service' ...

  1. #1

    Default Moving data from one table to another, (existing), one.


    Hi,

    I have a table, TABLEA ( ROWA[int], ROWB[int] ) and I want to move all
    the data to another table, TABLEB ( ROWA[int], ROWB[string],
    ROWC[string] )

    ROWC will be a constant string but ROWB will be converted to a string
    (VARCHAR 255) of the current INT value.

    How would you move the data from A to B?

    INSERT INTO TABLEB (
    ROWA,
    ROWB,
    ROWC
    )
    VALUES
    (
    SELECT ROWA, ROWB FROM TABLEA,
    'ROWC'.... )

    Not sure how that would work.

    Thanks

    FFMG


    --

    'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
    (http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
    (http://www.insurance-owl.com/other/car_rec.php)
    'Free URL redirection service' (http://urlkick.com/)
    ------------------------------------------------------------------------
    FFMG's Profile: http://www.httppoint.com/member.php?userid=580
    View this thread: http://www.httppoint.com/showthread.php?t=17868

    Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

    FFMG Guest

  2. #2

    Default Re: Moving data from one table to another, (existing), one.

    On 27 Jun, 06:31, FFMG <httppoint.com> wrote: 

    I assume you actually mean COLA, COLB, COLC as you are talking about
    columns and not rows.
    You also have to use the correct sytax as shown in the manual for
    INSERT ... SELECT
    http://dev.mysql.com/doc/refman/5.0/en/insert.html

    You would write it like this:

    INSERT INTO TABLEB (
    ROWA,
    ROWB,
    ROWC
    )
    SELECT ROWA, ROWB, 'ROWC' FROM TABLEA

    OR more correctly conceptwise:

    INSERT INTO TABLEB (
    COLA,
    COLB,
    COLC
    )
    SELECT COLA, COLB, 'COLC' FROM TABLEA

    Captain Guest

  3. #3

    Default Re: Moving data from one table to another, (existing), one.


    Captain Paralytic;78563 Wrote: 

    Thanks, you are right I was talking about COLA/B/C

    I just want to make sure that COLB will not be a problem as I am
    inserting an INT into a VARCHAR(255). I don't need to convert
    anything?

    FFMG


    --

    'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
    (http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
    (http://www.insurance-owl.com/other/car_rec.php)
    'Free URL redirection service' (http://urlkick.com/)
    ------------------------------------------------------------------------
    FFMG's Profile: http://www.httppoint.com/member.php?userid=580
    View this thread: http://www.httppoint.com/showthread.php?t=17868

    Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

    FFMG Guest

  4. #4

    Default Re: Moving data from one table to another, (existing), one.

    FFMG wrote: 
    >
    > Thanks, you are right I was talking about COLA/B/C
    >
    > I just want to make sure that COLB will not be a problem as I am
    > inserting an INT into a VARCHAR(255). I don't need to convert
    > anything?
    >
    > FFMG[/ref]

    I always find the best way to be sure of these things is to knock up test
    tables and try it out!


    Paul Guest

  5. #5

    Default Re: Moving data from one table to another, (existing), one.


    Paul Lautman;78676 Wrote: 
    > >
    > > Thanks, you are right I was talking about COLA/B/C
    > >
    > > I just want to make sure that COLB will not be a problem as I am
    > > inserting an INT into a VARCHAR(255). I don't need to convert
    > > anything?
    > >
    > > FFMG[/ref]
    >
    > I always find the best way to be sure of these things is to knock up
    > test
    > tables and try it out![/ref]

    Yes, that's what I did, and it does work.

    But my question was kind of 'best practice'.

    Because I am sure that moving a float to a string might cause some
    problems.
    (Just guessing here).

    It might work, but is it the right way?

    FFMG


    --

    'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
    (http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
    (http://www.insurance-owl.com/other/car_rec.php)
    'Free URL redirection service' (http://urlkick.com/)
    ------------------------------------------------------------------------
    FFMG's Profile: http://www.httppoint.com/member.php?userid=580
    View this thread: http://www.httppoint.com/showthread.php?t=17868

    Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

    FFMG Guest

  6. #6

    Default Re: Moving data from one table to another, (existing), one.

    On 28 Jun, 06:15, FFMG <httppoint.com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Yes, that's what I did, and it does work.
    >
    > But my question was kind of 'best practice'.
    >
    > Because I am sure that moving a float to a string might cause some
    > problems.
    > (Just guessing here).
    >
    > It might work, but is it the right way?
    >
    > FFMG
    >
    > --
    >
    > 'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
    > (http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
    > (http://www.insurance-owl.com/other/car_rec.php)
    > 'Free URL redirection service' (http://urlkick.com/)
    > ------------------------------------------------------------------------
    > FFMG's Profile:http://www.httppoint.com/member.php?userid=580
    > View this thread:http://www.httppoint.com/showthread.php?t=17868
    >
    > Message Posted via the webmaster forumhttp://www.httppoint.com, (Ad revenue sharing).- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    The "right" way I guess is to use a CAST function and some SQL
    implementations may require you to do this. MySQL however will handle
    the casting for you.

    Captain Guest

Similar Threads

  1. Replies: 0
    Last Post: June 1st, 03:15 AM
  2. Create table overwrites existing table in mssql
    By bmyers in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 25th, 10:00 AM
  3. check if the table is already existing
    By Merlyn MM in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 4th, 01:59 AM
  4. Creating new table with same structure as existing one
    By Nebojsa Sevo in forum Informix
    Replies: 3
    Last Post: October 14th, 09:56 PM
  5. How to place a new table around an existing one?
    By benleeke webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 15th, 12:17 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