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

  • (2 Pages)
  • +
  • 1
  • 2

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

#1 mnm458  Icon User is offline

  • D.I.C Head

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

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

Posted 16 June 2016 - 06:03 AM

INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table



Using this statement, i want to insert data from a few columns in a table to another table with those same columns. I want that data to be inserted in a pirticular pattern. How can i acheive that? something like this, if u get what i mean?

Mark Id   Student ID    Question ID       Marks
 

 1         abc               1
 2         abc               2
 3         abc               3
 4         djf               1
 5         djf               2
 6         djf               3




The Mark column will be filled by teacher, rest all should be filled. Also, i want, whenever the data is opened from the table into VB, it should check for any changes in the data from the previous table where the data came from originally, and make those changes accordingly.


Thanks!

Is This A Good Question/Topic? 0
  • +

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

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13566
  • View blog
  • Posts: 54,125
  • Joined: 12-June 08

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

Posted 16 June 2016 - 06:45 AM

... so is that not working for some reason?
Was This Post Helpful? 0
  • +
  • -

#3 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

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

Posted 16 June 2016 - 10:39 AM

Why do you care what order it is put into the table? The order should be displayed when the data is selected.
Was This Post Helpful? 1
  • +
  • -

#4 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 16 June 2016 - 04:15 PM

Ok i'll try and report back with results.
Was This Post Helpful? 0
  • +
  • -

#5 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 16 June 2016 - 06:57 PM

Nop. it wouldnt work like that. I have a Questions table, Student table and a Test table.

In the Mark table, i need to insert values from the three tables above, in a pattern (maybe).

here's a sample.

Student Table

Student ID       StudentFname        StudentLname      StudentPassword       ClassID

JAL0001          Jack                Allison           abfjfc                12A
LBR0014          Lisa                Brown             jbsbfjb               12A              



Question Table
Question ID     TestID    Marks

Q1A             T1        2
Q1B             T1        3
Q2Ai            T1        5
Q1A             T2        4



Test Table

TestID        TestName

 T1           Functions

 T2           Calculus 

 T3           Probability 



These are the three tables with sample data. Now in the mark table,

MarkID   TestID   StudentID   QuestionID    Marks

1         T1      JAL0001      Q1A       
2         T1      JAL0001      Q1B
3         T1      JAL0001      Q2Ai
4         T1      LBR0014      Q1A
5         T1      LBR0014      Q1B
6         T1      LBR0014      Q2Ai



MarkID is the primary key and is auto incremented. the data should be inserted into TestID, StudentID and QuestionID. If you notice, there is a pattern, and it is logical. It is supposed to be like that. How do i acheive this, from VB.net. Basically what query to use?

Thanks.

This post has been edited by mnm458: 16 June 2016 - 07:31 PM

Was This Post Helpful? 0
  • +
  • -

#6 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 446
  • View blog
  • Posts: 2,179
  • Joined: 07-April 08

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

Posted 17 June 2016 - 08:29 AM

Like @ybadragon said the order in which the data is stored should not matter in the least bit. You can manipulate the data when retrieving it how ever you feel like doing it. What happens when you add a new question? since your PK is an auto-increment that new row will appear at the end of your data set if you do a simple select. So again the order in which the data is stored should not matter. If you want it to look that way <When Retrieving> the data then just add an order by clause to your query:

SELECT
    TestID
,   StudentID
,   QuestionID
,   Marks
FROM Marks
ORDER BY TestID, StudentID, QuestionID


Was This Post Helpful? 0
  • +
  • -

#7 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 - 02:11 AM

can you tell what the query's gonna look like?
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6410
  • View blog
  • Posts: 25,906
  • Joined: 12-December 12

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

Posted 18 June 2016 - 02:21 AM

Did you adapt it and try and run it to see the output? You are in the best position to do this.
Was This Post Helpful? 0
  • +
  • -

#9 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 - 07:09 AM

i cant find an example on the web for insert into for multiple tables. here's what i have so far. i know its wrong

INSERT INTO Marks ( TestID, StudentID, QuestionID) 
    SELECT  TestID, StudentID, QuestionID, Marks
    FROM Student "
    INNER JOIN MARKS ON MARKS.StudentID = Student.StudentId 
    INNER JOIN MARKS ON MARKS.QuestionId = Questions.Id "
    INNER JOIN MARKS ON MARKS.TestId = Questions.TestID


This post has been edited by mnm458: 18 June 2016 - 07:10 AM

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6410
  • View blog
  • Posts: 25,906
  • Joined: 12-December 12

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

Posted 18 June 2016 - 07:28 AM

(Assuming all students are sitting all tests?)

You don't need to involve the Tests table, as your statement attempt shows. But you aren't matching anything in the Marks table, you are just inserting new rows.

So you have

INSERT INTO Marks ( TestID, StudentID, QuestionID)
-- select fields from Student and Question
-- with a join connecting Student to Question

The TestID is in the Question table.

Questions or Question? You'd be better off sticking to either all single, or all multiple, names, preferably single: Student, Question, Mark. (I often use multiples even though I understand the preference is for single ;). Microsoft et al. are also inconsistent in this.)
Was This Post Helpful? 0
  • +
  • -

#11 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 - 07:50 AM

haha yeah, i actually dont have test table in the statement, i get it off form the Questions table. :)

But i am really stuck on the statement.

SELECT  TestID, StudentID, QuestionID, Marks
FROM Student



It attempts to select the data form Student table but the student table only has Student ID, the question table has TestID and Question ID. Please indicate me how to fix this :)

Can you give me a sample of the full statement, how it should be?

thanks a lot Andrew :)
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6410
  • View blog
  • Posts: 25,906
  • Joined: 12-December 12

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

Posted 18 June 2016 - 08:00 AM

You need to take the time to study the syntax when referring to more than one table. For example,

SELECT Question.TestID, Student.StudentID, Question.QuestionID
FROM etc

The column-names don't actually need to be qualified with the table-names if they are unique within the dataset, but it is best to qualify, especially as more fields are included.

I've removed the Marks column as you have no values to insert for it, or from anywhere.
Was This Post Helpful? 0
  • +
  • -

#13 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 - 08:06 AM

OHH right! :D You know i have limited time to complete a project. it is made in VB but set around Databases, i am trying to get the hang of it. it will take some time and research for me to get better :P

This is sorted, and what about this?

  INNER JOIN MARKS ON MARKS.StudentID = Student.StudentId
    INNER JOIN MARKS ON MARKS.QuestionId = Questions.Id "
    INNER JOIN MARKS ON MARKS.TestId = Questions.TestID




By looking at these 3 statements, are they correct?
Was This Post Helpful? 0
  • +
  • -

#14 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6410
  • View blog
  • Posts: 25,906
  • Joined: 12-December 12

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

Posted 18 June 2016 - 08:14 AM

If it is sorted then please post your statement so that anyone else who reads this can have "closure".

I don't know what you are attempting with that statement because the INSERT statement doesn't need anything from Marks other than the column names (and order).
Was This Post Helpful? 1
  • +
  • -

#15 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 - 08:18 AM

http://stackoverflow...multiple-tables

Please refer to this. They included inner join to insert data into table. Should i ignore it?

EDIT* What would i put in 'etc' place? cause there are multiple table.

This post has been edited by mnm458: 18 June 2016 - 08:26 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2