Results 1 to 4 of 4
  1. #1
    Join Date
    May 2016
    Posts
    6

    Unanswered: How To Insert Data From One Table To Another Table In A Pattern?

    Code:
    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?

    I have a Questions table, Student table and a Test table. In the Mark table, i need to insert values from the three tables, in a pattern (maybe).

    here's a sample.

    Student Table

    Code:
    Student ID       StudentFname        StudentLname      StudentPassword       ClassID
    
    JAL0001           Jack                    Allison              abfjfc               12A
    LBR0014          Lisa                     Brown              jbsbfjb               12A
    Question Table

    Code:
    Question ID     TestID    Marks
    
    Q1A               T1        2
    Q1B               T1        3
    Q2Ai              T1        5
    Q1A               T2        4
    Test Table

    Code:
    TestID        TestName
    
     T1          Functions
    
     T2          Calculus
    
     T3          Probability
    These are the three tables with sample data. Now in the mark table,
    Code:
    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.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    That's not how SQL is supposed to work. The data are not generally ordered within a table. However, if you use an ORDER BY clause on your INSERT INTO statement, you might achieve this.

    You'd be better off letting the data get into the table however the query optimiser decides, and the writing a view over it that sorts them into your desired order.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2016
    Posts
    6
    Quote Originally Posted by weejas View Post
    That's not how SQL is supposed to work. The data are not generally ordered within a table. However, if you use an ORDER BY clause on your INSERT INTO statement, you might achieve this.

    You'd be better off letting the data get into the table however the query optimiser decides, and the writing a view over it that sorts them into your desired order.
    what about the query. how should my insert query look like/ because i am getting data from multiple tables

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    There is no relation between one one side the Question and Test tables and on the other side the Student table. The only thing you can generate is all valid Question/Test-combinations combined with each and every student in the Student table. Including the students from last year, the year before that, the year before that, ... till Adam and Eve, if they were students in your school.

    Code:
    CREATE TABLE dbo.Student (
    	Student_ID	CHAR(10)	NOT NULL,
    	StudentFname	NVARCHAR(50),
    	StudentLname	NVARCHAR(50),
    	StudentPassword	NVARCHAR(255),
    	ClassID			CHAR(5),
    	CONSTRAINT pk_student	PRIMARY KEY (Student_ID)
    )
    
    INSERT INTO Student(Student_ID, StudentFname, StudentLname, StudentPassword, ClassID) VALUES
    ('JAL0001', 'Jack', 'Allison', 'abfjfc', '12A'),
    ('LBR0014', 'Lisa', 'Brown', 'jbsbfjb', '12A');
    
    CREATE TABLE dbo.Question (
    	Question_ID		CHAR(10)	NOT NULL,
    	TestID	NVARCHAR(50)	NOT NULL,
    	Marks	DEC(10, 2)		NOT NULL,
    	CONSTRAINT pk_Question	PRIMARY KEY (Question_ID, TestID)
    )
    
    INSERT INTO Question(Question_ID, TestID, Marks) VALUES
    ('Q1A', 'T1', 2),
    ('Q1B', 'T1', 3),
    ('Q2Ai', 'T1', 5),
    ('Q1A', 'T2', 4);
    
    CREATE TABLE dbo.Test (
    	TestID	NVARCHAR(50)	NOT NULL,
    	TestName	NVARCHAR(255)		NOT NULL,
    	CONSTRAINT pk_Test	PRIMARY KEY (TestID)
    )
    
    INSERT INTO Test(TestID, TestName) VALUES
    ('T1', 'Functions'),
    ('T2', 'Calculus'),
    ('T3', 'Probability');
    
    CREATE TABLE dbo.Mark (
    	MarkID	int	IDENTITY(1, 1)	NOT NULL,
    	TestID	NVARCHAR(50)	NOT NULL,
    	StudentID	CHAR(10)	NOT NULL,
    	QuestionID	CHAR(10)	NOT NULL,
    	mark		DEC(10, 2),
    	CONSTRAINT pk_Mark	PRIMARY KEY (MarkID)
    )
    
    INSERT INTO Mark(TestID, StudentID, QuestionID)
    SELECT T.TestID, S.Student_ID, Q.Question_ID
    FROM dbo.Test as T
    	INNER JOIN dbo.Question as Q ON
    		T.TestID = Q.TestID
    	LEFT OUTER JOIN dbo.Student as S ON
    		1 = 1
    ORDER BY T.TestID, S.Student_ID, Q.Question_ID
    
    SELECT *
    FROM dbo.Mark
    
    --Result: 
    --MarkID	TestID	StudentID	QuestionID	mark
    --1		T1	JAL0001   	Q1A       	NULL
    --2		T1	JAL0001   	Q1B       	NULL
    --3		T1	JAL0001   	Q2Ai      	NULL
    --4		T1	LBR0014   	Q1A       	NULL
    --5		T1	LBR0014   	Q1B       	NULL
    --6		T1	LBR0014   	Q2Ai      	NULL
    --7		T2	JAL0001   	Q1A       	NULL
    --8		T2	LBR0014   	Q1A       	NULL
    
    --Wanted:
    --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
    
    DROP TABLE dbo.Student;
    DROP TABLE dbo.Question;
    DROP TABLE dbo.Test;
    DROP TABLE dbo.Mark;
    You need an extra table that covers the relation between a Student And all the Tests (s)he has to do in a certain year.

    Don't spend your time with inserting data "in a certain pattern" - as I did -. Order the data as requested in the lists you will generate later.
    Last edited by Wim; 06-19-16 at 04:34.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

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