Insert values in a table based on another table

Question

0.00/5 (No votes)

See more:

Hi,

I need to write a Stored Procedure.
I have a table with some data now I need to insert relevant data into another table according to some condition

Example:
Table 1

Name     Class Math Physics English
Alok     V     60   50      45
Bobby    V     78   87      86
Chandini VI    56   76      56
Dolly    VII   87   56      66

[Based on this condition
Insert Values into Table2

If class =V  
(Table2.Physics=Select sum(Table1.Physics) from Table1 where Class like ‘V’
 Table2.Maths=0 and table2.English=0)
If class =VI  
(Table2.Maths=Select sum(Table1.Maths) from Table1 where Class like ‘VI’
 Table2.Physics=0 and table2.English=0)
If class =VI  
(Table2.English =Select sum(Table1.English) from Table1 where Class like ‘VII’
 Table2.Physics=0 and table2.Maths =0)
]

Table 2

Class Math Physics English
V     0    137     0
VI    56   0       0
VII   0    0       66

Kindly Help me out

Posted 28-Mar-11 3:42am

manushi88

Updated 28-Mar-11 5:52am

Dalek Dave

v2

Add a Solution

Comments

Sandeep Mewara 28-Mar-11 11:11am

What kind of help you are seeking for? Tried anything? Stuck somewhere?

Dalek Dave 28-Mar-11 11:53am

Edited for Code Block and Readability.

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)

walterhevedeich · Answer 1 · 2011-03-28T05:21:00

Solution 1

I used to give codes to these kinds of question. Then I realized, I am not helping out because I am spoonfeeding the one who is asking. Anyway, what you can do is to do an insert with a select. For example

INSERT INTO TABLE2(<FIELDS>) SELECT <FIELDS> FROM TABLE1 WHERE...

Try that one out and if you get stuck, come back and ask questions.

Posted 28-Mar-11 5:21am

walterhevedeich

Comments

Dalek Dave 28-Mar-11 11:53am

Earns a 5.

Wendelius · Answer 2 · 2011-03-28T05:23:00

This can be done in many ways but have you tried for example something like (translated from your description):

SQL

INSERT INTO Table2 (Class, Math, Physics, ...)
SELECT Class,
       CASE
          WHEN Class = 'VI' THEN (SELECT SUM(t1.Math) FROM Table1 t1 WHERE t1.Class = t.Class)
          ELSE 0
       END,
       CASE
          WHEN Class = 'V' THEN (SELECT SUM(t1.Physics) FROM Table1 t1 WHERE t1.Class = t.Class)
          ELSE 0
       END,
       ...
FROM Table1 t