Insert values in a table based on another table

Question

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

see if this helps blog.sqlauthority.com/2007/08/15/…

ajm
– ajm

2011-03-28 13:32:05 +00:00
Commented Mar 28, 2011 at 13:32 — ajm
– ajm, Commented Mar 28, 2011 at 13:32

Quassnoi · Accepted Answer · 2011-03-28 13:47:28Z

3

INSERT
INTO    table2
SELECT  class,
        SUM(CASE WHEN class = 'V' THEN Physics ELSE 0 END),
        SUM(CASE WHEN class = 'VI' THEN Math ELSE 0 END),
        SUM(CASE WHEN class = 'VII' THEN English ELSE 0 END)
FROM    table1
GROUP BY
        class

answered Mar 28, 2011 at 13:47

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jkj · Accepted Answer · 2011-03-28 14:41:19Z

0

Do you have to use a stored procedure or even another table?

A view servers you better as it can never go off sync with the real data. (There is no real performance issue either.)

CREATE VIEW table2 AS
SELECT class,
       SUM(CASE WHEN table1.class = 'V' THEN table1.physics ELSE 0 END),
       SUM(CASE WHEN table1.class = 'VI' THEN table1.math ELSE 0 END),
       SUM(CASE WHEN table1.class = 'VII' THEN table1.english ELSE 0 END)
FROM table1
GROUP BY class

answered Mar 28, 2011 at 14:41

jkj

2,6111 gold badge18 silver badges24 bronze badges

1 Comment

jkj Over a year ago

This is the same as @Quassnoi 's solution but with a view.

Collectives™ on Stack Overflow

Insert values in a table based on another table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related