Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts

    copy data between different tables with lots of fields

    hi guys

    i have had to update a massive subcontractor section on our management system.

    some fields are the same but many are different. basically i want to copy the old table's data into the new one, but having hundreds of fields i dont want to have to specify each field individually.

    can i do it so that it just copies fields that are common to both?
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you can query the INFORMATION_SCHEMA database and that would give you a list of columns. that would at least save you typing them out.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,148
    Thanks
    80
    Thanked 4,557 Times in 4,521 Posts
    Actually, you don't *NAVE* to specify field names at all in an INSERT. So long as the VALUES( ) section has one value for each field.

    But miss one field and you are hosed.

    Personally, I'd bite the bullet and do it right. You can indeed use the INFORMATION_SCHEMA, but it's not going to help you figure out that WIDGET in table 1, field 37, needs to go into FRAMITZ in table 2, field 103.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I was guessing that fields "common to both" could have same names. But really no straightforward way to do this.

  • Users who have thanked guelphdad for this post:

    babelfish (08-09-2011)

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    I was guessing that fields "common to both" could have same names. But really no straightforward way to do this.
    yes, they do, all fields that are common are exactly the same. annoying there is no easy way to do this.
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    isnt there just a simple way to merge 2 tables?

    every method ive seen wants me to have to list 200+ columsn manually. what a ball ache.
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    again you don't have to list them manually, query the INFORMATION_SCHEMA database, specifically the COLUMNS table. table_schema will tell you which table your columns belong to.

    So you can do:
    Code:
    SELECT column_name FROM information_schema.columns
    WHERE
    table_scheme = 'thenameofyourtablegoeshere'
    Dump that to an outfile and then it saves you typing the column names out.

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    again you don't have to list them manually, query the INFORMATION_SCHEMA database, specifically the COLUMNS table. table_schema will tell you which table your columns belong to.

    So you can do:
    Code:
    SELECT column_name FROM information_schema.columns
    WHERE
    table_scheme = 'thenameofyourtablegoeshere'
    Dump that to an outfile and then it saves you typing the column names out.
    hmm...

    i take it you meant table.schema? even so i get empty set....

    SELECT column_name FROM information_schema.columns WHERE table_schema = 'subcontractors_old';

    more digging needed....
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #9
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    again you don't have to list them manually, query the INFORMATION_SCHEMA database, specifically the COLUMNS table. table_schema will tell you which table your columns belong to.

    So you can do:
    Code:
    SELECT column_name FROM information_schema.columns
    WHERE
    table_scheme = 'thenameofyourtablegoeshere'
    Dump that to an outfile and then it saves you typing the column names out.
    hmm...

    i take it you meant table.schema? even so i get empty set....

    SELECT column_name FROM information_schema.columns WHERE table_schema = 'subcontractors_old';

    more digging needed....

    edit:

    SELECT column_name FROM information_schema.columns WHERE table_schema = 'dbname' and TABLE_NAME = 'table_name';

    works.
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #10
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    ok, im being dumb now.

    i have both lists of table fields, but whats the best way to check lists of 200+ fields against each other to see which are common?

    sorry if this is a newbie question, wife is pregnant and after 6 months of sleeping for 3 or 4 hours a night i feel like a lobotomised zombie!
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #11
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #12
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    all done, thanks for your help guys!

    the lobotomized zombie got it all working!
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson


  •  

    Posting Permissions

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