Reputation: 11
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
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
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
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