How to Insert output of one query to another table

Question

0.00/5 (No votes)

See more:

SQL

insert into a(aa,b,c,d)
  SELECT @curRank :=IF(@prevVal= exammarks.Test1, @curRank, @studentN) AS rank,
@percentile := IF(@prevVal=exammarks.Test1 , @percentile,(@totalS - @studentN + 1)/(@totalS)*100) as percent,
@studentN := @studentN + 1 as studentN,
@prevVal:=exammarks.Test1 as totagain
FROM student,exammarks, (
SELECT @curRank :=0, @prevVal:=null, @studentN:=1, @percentile:=100 ) r
WHERE student.idstudent = exammarks.stdid
ORDER BY exammarks.Test1  DESC

I can't do above operation.

Actually what i want is insert the result of select query to the table named 'a'. Select query separately works fine.

they are not true columns, but virtual columns which i select using some calculations.
How can i insert those data to another table, or else how can i view them using a view?

If all of them not possible how can i get those values in crystal reports by calling a formula or any other method.iam stucked.

please help..

Posted 8-May-13 8:09am

Muthu Karunarathna

Updated 8-May-13 17:09pm

Kenneth Haugland

v4

Add a Solution

Comments

db7uk 8-May-13 14:31pm

this is not c#, please tag question correctly. What version of SQL is it (if it is sql at all)? information like this really helps us help you.

Muthu Karunarathna 8-May-13 20:38pm

Yes.Iam sorry that i have put wrong tag.iam using mysql.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Amy · Accepted Answer · 2013-05-08T18:41:00

Solution 2

Try giving same Alias Names. See this:

SQL

insert into a(aa,b,c,d)
  SELECT @curRank :=IF(@prevVal= exammarks.Test1, @curRank, @studentN) AS aa,
@percentile := IF(@prevVal=exammarks.Test1 , @percentile,(@totalS - @studentN + 1)/(@totalS)*100) as b,
@studentN := @studentN + 1 as c,
@prevVal:=exammarks.Test1 as d
FROM student,exammarks, (
SELECT @curRank :=0, @prevVal:=null, @studentN:=1, @percentile:=100 ) r
WHERE student.idstudent = exammarks.stdid
ORDER BY exammarks.Test1  DESC

Hope it helps!

--Amit

Posted 8-May-13 18:41pm

_Amy

Comments

Muthu Karunarathna 9-May-13 5:50am

This also not working.I dnt knw what to do..my main requirement is getting student ranks accroding to thier marks and view them using crystal report.above query which i used is showing ranks correctly.but i have no idea how can i view them through crystal reports.because it can't insert to db.without above formula,if there is another method which i can calculate ranks through crysatal report please show me the way.

Ess Kay · Accepted Answer · 2013-05-08T08:23:00

try using join

SQL

select * from table 1

join

SELECT @curRank :=IF(@prevVal= exammarks.Test1, @curRank, @studentN) AS rank,
@percentile := IF(@prevVal=exammarks.Test1 , @percentile,(@totalS - @studentN + 1)/(@totalS)*100) as percent,
@studentN := @studentN + 1 as studentN,
@prevVal:=exammarks.Test1 as totagain
FROM student,exammarks, (
SELECT @curRank :=0, @prevVal:=null, @studentN:=1, @percentile:=100 ) r
WHERE student.idstudent = exammarks.stdid
ORDER BY exammarks.Test1  DESC