0

I have 3 tables in my SQL Server database named College, University, OldSyllabus.

Table College has 1000 rows with columns such as DateOfJoining, studentName

Table University has 50 rows with columns such as DateOfCourseRevision, courseName

I need to write an insert statement to push records in table OldSyllabus by comparing University table DateOfCourseRevision column with College table DateOfJoining column

All records from College table to be inserted into OldSyllabus by comparing the DateOfCourseRevision <= DateOfJoining

Query:

INSERT INTO [OS].[OldSyllabus] (StudenName, Address) 
VALUES 
    ((SELECT C.Name 
      FROM [COL].[College] AS c 
      INNER JOIN [UNI].[University] AS u ON c.CourseName = u.CourseName 
                                         AND c.Date <= u.Date),
     (SELECT C.Address  
      FROM [COL].[College] AS c 
      INNER JOIN [UNI].[University] AS u ON c.CourseName = u.CourseName 
                                         AND c.Date <= u.Date))

I get an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Avinesh
  • 21
  • 5
  • 2
    What have you tried and what is not working? Also, what is your question? – squillman May 31 '19 at 14:56
  • 1
    Also what about the error don't you understand? Compared to some of the other errors from SQL Server, it is one of the more clear and explicit ones on what the problem is. – Thom A May 31 '19 at 15:00
  • Perhaps sharing your query would help those of us who can't see your screen? – Sean Lange May 31 '19 at 15:03
  • I sense this is for school but something seems a bit off when you insert into a column StudentName and the value is the name from the College table. Also, is your professor really making you use a separate schema for every table? – Sean Lange May 31 '19 at 15:10
  • Hi Sean I just wrote a quick sample query which exactly resembles my original query except for the fact did some mistakes on schema name. But apart from schema my rest of the code is what i am struggling with – Avinesh May 31 '19 at 15:15
  • Now that you changed the insert statement you have even more issues. How would you know that the name and the address are the same entity? You want to use a single query, not one per column – Sean Lange May 31 '19 at 15:21
  • Hi Sean, Can you explain a bit pls – Avinesh May 31 '19 at 15:24

2 Answers2

2

What you're doing here, however, is mixing INSERT ...VALUES and INSERT...SELECT syntax. The documentation provides an example on how to perform a INSERT...SELECT statement: Inserting Data from Other Tables. You need to drop the parenthesis (()) around your SELECT, and remove the VALUES clause. So:

INSERT INTO [OS].[OldSyllabus] (StudenName) --Should that be StudentName? 
SELECT C.Name
FROM [COL].[College] AS c
     INNER JOIN [UNI].[University] AS u ON c.CourseName = u.CourseName
                                       AND c.Date <= u.Date;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Hi Larnu, Thanks for your suggestion I need to mention the ColumnName while inserting the record since i take few columns from the other table(s) and insert in OldSyllabus table I hope i answered your question – Avinesh May 31 '19 at 15:13
  • I didn't ask the question, @Avinesh, you did. This is my answer to **your** question. – Thom A May 31 '19 at 15:15
  • Yes it partially resolved, incase if i want to insert multiple columns how to do ?? In my example i mentioned about StudentName. But if i want to update studentName and StudentAddress ???? How to do??? – Avinesh May 31 '19 at 15:22
  • Add the extra columns to your `INSERT` and `SELECT` statement @Avinesh. I suggest reading the documentation I linked as it appears you aren't familiar with either the `SELECT` or `INSERT` statement syntaxes. you might want to look at some online tutorials as well; as `SELECT` is one of the basics of any SQL language. – Thom A May 31 '19 at 15:25
  • `SELECT Column1, Column2, Column3` [SELECT Examples (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-examples-transact-sql?view=sql-server-2017) – Thom A May 31 '19 at 15:35
  • INSERT INTO [OS].[OldSyllabus] (StudenName, Address) VALUES SELECT C.Name FROM [COL].[College] AS c INNER JOIN [UNI].[University] AS u ON c.CourseName = u.CourseName AND c.Date <= u.Date, SELECT C.Address FROM [COL].[College] AS c INNER JOIN [UNI].[University] AS u ON c.CourseName = u.CourseName AND c.Date <= u.Date – Avinesh May 31 '19 at 15:36
  • I am still unable to figure out on how to insert with subquery having more than one select statement – Avinesh May 31 '19 at 15:37
  • Why is there a `VALUES` in there @Avinesh...? I don't have `VALUES` in my answer and I specifically state *"and **remove** the VALUES clause"*. Please do take the time to read my answer. – Thom A May 31 '19 at 15:40
-1

Assuming the table oldsyllabus table has been created and has columns same as that of college, Use the query below to fetch the data from the college table and store:

INSERT INTO `oldsyllabus`(`DateofJoining`, `studentName`) SELECT college.DateofJoining, college.studentName FROM college JOIN university WHERE university.dateOfCourseRevision <= college.DateofJoining;

In case the table or columns haven't been created go to: Create table (structure) from existing table Here it explains how to create tables from existing ones

Bellatez
  • 1
  • 2