How to insert data from one table to another table in a pattern?

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 1430 Views - Last Post: 19 June 2016 - 04:16 AM

#16 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,570
  • Joined: 12-December 12

Re: How to insert data from one table to another table in a pattern?

Posted 18 June 2016 - 08:34 AM

This (from SO) is useful because it shows how to insert from other tables:
INSERT INTO table3 ( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, p.id, number, n.nationality
FROM table1 p
INNER JOIN table2 c ON c.Id = p.Id
INNER JOIN table3 n ON n.Id = p.Id

It is more than you need though because it is inserting from three tables.

You need to insert from two tables (to one table) but the syntax is the same.

"etc" is where you identify the tables and the join between them.

There are very many free SQL tutorials online, such as this chosen at random. Take the time to go through one and then return to your project, particularly as I don't intend to write your full statement for you ("fill in etc").
Was This Post Helpful? 1
  • +
  • -

#17 mnm458  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 06-December 15

Re: How to insert data from one table to another table in a pattern?

Posted 18 June 2016 - 09:10 PM

Ok, so i figured out the correct statement, thanks to you! I did not had to use Inner Join. here's the final correct statement:

 INSERT INTO Marks ( Questions.SacID, Student.StudentID, Questions.QuestionID) 
    SELECT  Questions.SacID, Student.StudentID, Questions.QuestionID 
    FROM Questions CROSS JOIN Student
   



But there is another problem. If the teacher changes data in any of the 2 tables (Student and Question) how do i account for that change in the Marks table? Do i use Update?
Was This Post Helpful? 0
  • +
  • -

#18 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,570
  • Joined: 12-December 12

Re: How to insert data from one table to another table in a pattern?

Posted 19 June 2016 - 12:30 AM

That should be INSERT INTO Marks (field1, field2, field3) where field1, etc., are field-names from the Marks table. (I'm surprised it works as you have it?)

None of the ID values should be changed, ever. The Teacher might correct, for example, the name of the Student, this would have no impact on the other tables as they are connected via the StudentID.

I was offering some assistance purely on the syntax of an INSERT INTO..SELECT statement. Starting to look at the bigger picture, there is a lot more planning that needs to be done.

Firstly, using the CROSS JOIN, if you add more tests and questions later and run the same query, it will (currently) reinsert ALL of the original details again. This is acceptable because, to only insert new question details, there could be a unique index on the combination of TestID (SacID?), StudentID and QuestionID in the Marks table. This unique index will prevent a student from being presented with the same set of questions a second (and third, etc.) time.

This caused me to notice that the QuestionID's are not unique. They aren't identifying a specific question (as ID's should) they are just, effectively, numbering the set of questions. So, in the Questions table, either the combination of SacID and QuestionID should form a compound key, or an additional autonumber/increment field could be added to uniquely identify specific questions, and this new value would be used in the INSERT, rather than SacID and QuestionID. I would take this route. The Marks table would then not have to hold both the QuestionID and Sac/TestID (and StudentID), just the (new) QuestionID and StudentID (and his/her Mark).

(This new QuestionID wouldn't be used in future queries to find the marks for a particular student, that search would be based on just the TestID and StudentID.)

Anyway, an ID value (QuestionID) should be unique within its table, and used as primary key (this is what everyone expects an ID to denote, and caused me to overlook that the QuestionID's weren't unique), and as much time as possible should be invested in planning. We need to plan taking into account what will happen going forward (and to try and anticipate changing requirements as well).
Was This Post Helpful? 0
  • +
  • -

#19 mnm458  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 06-December 15

Re: How to insert data from one table to another table in a pattern?

Posted 19 June 2016 - 03:51 AM

Oh naa, the question table has a primary key of SacID and QuestionID, so its unique.
Was This Post Helpful? 0
  • +
  • -

#20 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,570
  • Joined: 12-December 12

Re: How to insert data from one table to another table in a pattern?

Posted 19 June 2016 - 04:16 AM

Oh good. So you've considered everything then ;)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2