CODEWITHSUNDEEP
sqlsql-server
Fahad Satti
Fahad Satti

Reputation: 11

Copying data in temporary table in sql

I want to copy data in temp data from other table...

CREATE TABLE #temp1 (
    Question_ID INT
    ,Excellent INT
    ,Good INT
    ,Average INT
    ,Below_Average INT
    ,Poor INT
    )

INSERT INTO #temp1
VALUES (
    (
        SELECT Question_ID
        FROM Eval
        GROUP BY Question_ID
        )
    ,(
        SELECT COUNT(Answer_Marks) AS "Excellent"
        FROM Eval
        WHERE Answer_Marks = 5
            AND Emp_no = 'biit218'
        GROUP BY Question_ID
        )
    ,(
        SELECT COUNT(Answer_Marks) AS "Good"
        FROM Eval
        WHERE Answer_Marks = 4
            AND Emp_no = 'biit218'
        GROUP BY Question_ID
        )
    ,(
        SELECT COUNT(Answer_Marks) AS "Average"
        FROM Eval
        WHERE Answer_Marks = 3
            AND Emp_no = 'biit218'
        GROUP BY Question_ID
        )
    ,(
        SELECT COUNT(Answer_Marks) AS "Baverage"
        FROM Eval
        WHERE Answer_Marks = 2
            AND Emp_no = 'biit218'
        GROUP BY Question_ID
        )
    ,(
        SELECT COUNT(Answer_Marks) AS "poor"
        FROM Eval
        WHERE Answer_Marks = 1
            AND Emp_no = 'biit218'
        GROUP BY Question_ID
        )
    )

Each of subquery is supposed to copy 15 records in temp table but it returns an error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Upvotes: 0

Views: 486

Answers (3)

Taryn
Taryn

Reputation: 247880

You need to use INSERT INTO ... SELECT .. FROM to perform your insert. Your current syntax won't work because you are attempting to insert multiple rows into a single column. If you are using a subquery like that it should only return one value for all rows. I'd suggest using some conditional logic with an aggregate function to get each of those columns similar to:

INSERT INTO #temp1
SELECT 
    Question_ID,
    sum(case when Answer_Marks = 5 then 1 else 0 end) as Excellent,
    sum(case when Answer_Marks = 4 then 1 else 0 end) as Good,
    sum(case when Answer_Marks = 3 then 1 else 0 end) as Average,
    sum(case when Answer_Marks = 2 then 1 else 0 end) as Baverage,
    sum(case when Answer_Marks = 1 then 1 else 0 end) as Poor
FROM Eval
WHERE Emp_no = 'biit218'
GROUP BY Question_ID

Upvotes: 6

abeperl
abeperl

Reputation: 52

remove the "valus" keyword

INSERT INTO #temp1 SELECT ( SELECT Question_ID FROM Eval GROUP BY Question_ID ) ,( SELECT COUNT(Answer_Marks) AS "Excellent" FROM Eval WHERE Answer_Marks = 5 AND Emp_no = 'biit218' GROUP BY Question_ID ) ,( SELECT COUNT(Answer_Marks) AS "Good" FROM Eval WHERE Answer_Marks = 4 AND Emp_no = 'biit218' GROUP BY Question_ID ) ,( SELECT COUNT(Answer_Marks) AS "Average" FROM Eval WHERE Answer_Marks = 3 AND Emp_no = 'biit218' GROUP BY Question_ID ) ,( SELECT COUNT(Answer_Marks) AS "Baverage" FROM Eval WHERE Answer_Marks = 2 AND Emp_no = 'biit218' GROUP BY Question_ID ) ,( SELECT COUNT(Answer_Marks) AS "poor" FROM Eval WHERE Answer_Marks = 1 AND Emp_no = 'biit218' GROUP BY Question_ID )

Upvotes: -2

radar
radar

Reputation: 13425

you need to use insert into select syntax 

insert into #temp1
select question_id,
       count( case when Answer_Marks = 5 then 1 end ) as Excellent,
       count( case when Answer_Marks = 4 then 1 end ) as Good,
       count( case when Answer_Marks = 3 then 1 end ) as Average,
       count( case when Answer_Marks = 2 then 1 end ) as Baverage,
       count( case when Answer_Marks = 1 then 1 end ) as Poor
from Eval
where Emp_no ='biit218' and Answer_Marks between 1 and 5
group by Question_ID

Upvotes: 4

Related Questions