SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Copying certain rows from one database to another

    What is the best way to copy firstname, lastname from the users table in a database called oils over to the contacts table in the edownline database?

    Thank you!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you have a user that can connect to both databases then

    Code:
    insert into edownline (firstname, lastname)
    select
    firstname, lastname
    from
    oils

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    what he said, except with proper database qualification on the table names
    Code:
    INSERT 
      INTO edownline.contacts
         ( firstname
         , lastname )
    SELECT firstname
         , lastname
      FROM oils.users
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937, how come this query didn't work?

    Code:
    INSERT 
      INTO convert2silver.people
         ( IPaddress
         , dateAdded
         , firstName
         , lastname 
         , city
         , state
         , zip
         , country
         , email
         , career
         , referURL)
    SELECT IPaddress
         , date
         , firstname
         , lastname
         , city
         , state
         , zip
         , country
         , email
         , member
         , referURL
      FROM oils.users
    WHERE wantSilver = 'Yes';
    I didn't get an error message or anything.
    Convert your dollars into silver coins. www.convert2silver.com

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Most likely cause would be that there are no entries where "wantSilver" is "Yes".

    Are you running this in something like MySQL Workbench? If so, does it tell you have many rows are affected? Also, try just the select portion by itself and see what results you get.

  6. #6
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    845
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, I am using mySQL Workbench. I don't understand why I'm getting the following error. I checked that all permissions are granted to this user. Any ideas?

    Thanks!


    Error Code: 1142
    INSERT command denied to user 'recordAdmin'@'208.255.33.1' for table 'people'
    Convert your dollars into silver coins. www.convert2silver.com


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
  •