SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Moving Data Between Many Tables

    Hi,
    On moving data between tables:

    This is a problem I have where I want to move a record from an 'enquirer' table into a 'student' table, and then add their course interests from the old 'enquirer interests table' into the new 'student interests' table.

    In other words, I want to take a record from the enquirer table and pass this into the student table, whereby the enquirer becomes a student, and a new id for this student will be an auto incremented number. After this, I want to take the enquirer's (who has become a student) interests from the 'enquirer interests' table and place these interests into the 'student interest's' table, and the interests will be linked to the new id which was generated when the enquirer became a student.

    So far, I can pass the enquirers details into the student table, no problem. But how do i pass the interests from the enquirer interests table into the students interests table using the new incremented id that uniquely identified the student?

    I know sometimes people just select last id in the student table, but this is not recommended. Is there another way to link the students interests to the new student id?

    Thanks in advance,
    J

  2. #2
    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)
    have you thought about using just one table with a special column to distinguish enquirers from students? you wouldn't have to move anything then

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it good practise to use a column with values either 1 for student, and 0 for enquirer?

  4. #4
    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)
    yes, that works nicely
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •