Looping help in SQL required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Looping help in SQL required

Author

Topic

sajanjacobk
Starting Member

7 Posts

Posted - 2009-02-19 : 00:16:19

I have a two queries

1. (SELECT ID as studentID FROM tblstudent);

2. (SELECT SUM(`amount`) as sumamount FROM tbltransaction
WHERE `studentID`='10');
INSERT INTO `tbltransaction`
(`ID` ,`transactionTypeID`, `studentID`, `date`,`amount`,`addedBy`)
VALUES (NULL , '1', '10', '', amount, 'sumamount');

I would like to iterate query 2 against each studentID in query 1.
Could you please help me.

I am using mysql.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-19 : 00:41:50

insert into tbltransaction(ID ,transactionTypeID, studentID, date,amount,addedBy)
select null,1,studentid,getdate(),amount,(select sum(amount) from tbltransaction where studentid = 10) from tbltransaction where studentid = 10

sajanjacobk
Starting Member

7 Posts

Posted - 2009-02-19 : 00:59:38

Dear, I am thankful for the reply.

I need to use insert query in tbltransaction, against each ID in tblstudent.
With the help of some controls like FOR loop or WHILE loop. I am confused hw to use it.

In this case I can insert only one record (where studentid = 10)
I need to insert that many records in tbltransaction, corresponding to tblstudent.

Thank you very much for your heartful response.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 01:00:27

declare @amount int
select @amount = SUM(`amount`) as sumamount FROM tbltransaction WHERE `studentID`='10'

insert into tbltransaction(ID ,transactionTypeID, studentID, date,amount,addedBy)
select null,1,studentid,getdate(),amount, @amount

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-19 : 01:09:37

hi sajanjacobk, u can create a stored procedure and pass the studentid as input parameter
and then u can use the sp for insertion of records

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-19 : 02:14:20

hi,
is this what you want??

declare @id int
declare @tblstudent table(id int)
insert into @tblstudent values(10);
insert into @tblstudent values(20);
insert into @tblstudent values(30);
insert into @tblstudent values(40);

select @id=min(id) from @tblstudent
select @id
declare @tbltransaction table(ID int,transactionTypeID int,studentID int,date datetime,amount int ,addedBy int)
while(@id IS NOT NULL)
begin
insert into @tbltransaction
select null,1,@id,getdate(),2000,(select sum(amount) as 'tamt' from @tbltransaction)
select @id=min(id) from @tblstudent where id >@id
end
select * from @tbltransaction

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-19 : 03:26:36

seeme like this is what you want

INSERT INTO tbltransaction
(ID ,transactionTypeID, studentID, date,amount,addedBy)
SELECT NULL,'1',t.studentID,'',SUM(`amount`) as sumamount,'sumamount'
FROM tbltransaction t
JOIN tblstudent s
ON s.ID=t.studentID
GROUP BY t.studentID

sajanjacobk
Starting Member

7 Posts

Posted - 2009-02-19 : 07:11:05

Thank you visakh16, for the help. It works well

Actually my idea is to add the interest amount to the transaction table against all the accounts in the students table.
using the simple interest formula PRT/100.

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 10:58:24

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources