Looping through a table without cursors

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 through a table without cursors

Author

Topic

subashinikumar
Starting Member

10 Posts

Posted - 2009-07-24 : 05:46:10

I have a table containing user id, value1,value 2
i have to loop through the id of that table and retrieve its value from another two table.
but how can i achieve it without using cursor.

step1.i have to take one id from table 4
then its correcponding row from table 1 and insert into table3.
then its correcponding row from table 2 and insert
again into table 3
step2 : repeat step 1 for the next id from table 4

This is what i have to do

Subashini Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 05:52:39

insert into table3(col1,col2,...)
select col1,col2,...from table4 as t4 where exists(select * from table1 where id=t4.id)
union all
select col1,col2,...from table4 as t4 where exists(select * from table2 where id=t4.id)

Madhivanan

Failing to plan is Planning to fail

subashinikumar
Starting Member

10 Posts

Posted - 2009-07-24 : 05:56:28

but will this insert one by one for each id?
Row 1 from table2
Row 1 from table3
row2 from table 2
row 2 from table 3

like this??

Subashini Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 06:01:36

quote:
Originally posted by subashinikumar

but will this insert one by one for each id?
Row 1 from table2
Row 1 from table3
row2 from table 2
row 2 from table 3

like this??

Subashini Kumar

Yes. Try and see

Madhivanan

Failing to plan is Planning to fail

subashinikumar
Starting Member

10 Posts

Posted - 2009-07-24 : 06:38:14

no.its not working.its looping thru the first query(The query before union all) and finishes it and then coming to the second query(the query after union all) and then loops thru it.
It not working

Subashini Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 06:41:08

quote:
Originally posted by subashinikumar

no.its not working.its looping thru the first query(The query before union all) and finishes it and then coming to the second query(the query after union all) and then loops thru it.
It not working

Subashini Kumar

Thats the way it should work. What do you mean by "its not working"
Post some sample data and expected result

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-24 : 06:41:17

How can you tell?

Does table3 contain an identity column?

You know that sql server doesn't guarantee order except with an ORDER BY clause?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

subashinikumar
Starting Member

10 Posts

Posted - 2009-07-24 : 06:47:37

Input Table Name : Tax_Detail

Tax_Id Date Tax_Name User_Id ClientId Project_Id Total_Amount Tax_Percent Tax_Amount
1 07/17/2009 Tax1 1 1 1 100 10 10
2 07/18/2009 Tax2 2 1 1 110 10 11
3 07/19/2009 Tax3 3 1 1 110 10 11
4 07/20/2009 Tax4 4 1 1 110 10 11
5 07/21/2009 Tax5 5 1 1 110 10 11

Expected Output

07/17/2009 Tax1 1 1 1 100 10 10
Total: 100 Total: 10
07/18/2009 Tax2 2 1 1 110 10 11
Total: 110 Total: 11
07/19/2009 Tax3 3 1 1 110 10 11
Total: 110 Total: 11
07/20/2009 Tax4 4 1 1 110 10 11
Total: 110 Total: 11
07/21/2009 Tax5 5 1 1 110 10 11
Total: 110 Total: 11
Grand Total: 540 Grand Total: 54

where this total is for each user id .one user id may have many rows in the input table

Subashini Kumar

subashinikumar
Starting Member

10 Posts

Posted - 2009-07-24 : 06:53:14

create table tax_detail
(
tax_id int primary key identity,
date datetime ,
tax_name varchar(20) ,
user_id1 int ,
clientid int ,
project_id int ,
total_amount int ,
tax_percent int,
tax_amount int
)

create table output_table
(
date varchar(20) ,
tax_name varchar(20) ,
user_id1 varchar(20) ,
clientid varchar(20) ,
project_id varchar(20) ,
total_amount varchar(20) ,
tax_percent varchar(20),
tax_amount varchar(20)
)

select user_id1 as id,sum(total_amount) as total_amt,sum(tax_amount)as total_tax_amt into #total_user from tax_detail group by user_id1
select sum(total_amt) as total_amt,sum(total_tax_amt)as total_tax_amt into #grand_total from #total_user;
declare user_id1 cursor for select id from #total_user;
declare @id int;
declare @status int;
open user_id1
fetch next from user_id1 into @id;
set @status=@@fetch_status;
while(@status=0)
begin
insert into output_table
select
date,
tax_name ,
user_id1 ,
clientid ,
project_id ,
total_amount ,
tax_percent ,
tax_amount
from tax_detail where tax_detail.user_id1 = @id;
insert into output_table
select '','','','',
'Total ',total_amt,'Total',total_tax_amt from #total_user where #total_user.id=@id
fetch next from user_id1 into @id;
set @status=@@fetch_status;
end
insert into output_table select '','','','','Grand Total',total_amt,'Grand total',total_tax_amt from #grand_total
close user_id1
deallocate user_id1

This is how i did.But now i wanted it to be done without cursor

Subashini Kumar

Subashini Kumar

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-24 : 07:38:46

OK -- you totally shouldn't do this in sql

Do this in your front end application. It will be a lot easier. You have to completely screw with the data types and format for this to be done in sql and there is no good way to do it.

As an example just look how nasty it is to get this kind of output.


DECLARE @foo TABLE (
	[Tax_Id] INT
	, [Date] DATETIME
	, [Tax_Name] CHAR(4)
	, [User_Id] INT
	, [Client_Id] INT
	, [Project_Id] INT
	, [Total_Amount] MONEY
	, [Tax_Percent] FLOAT
	, [Tax_Amount] MONEY
	)

INSERT @foo
      SELECT 1, '20090717', 'Tax1', 1, 1, 1, 100, 10, 10
UNION SELECT 2, '20090718', 'Tax2', 2, 1, 1, 110, 10, 11
UNION SELECT 3, '20090719', 'Tax3', 3, 1, 1, 110, 10, 11
UNION SELECT 4, '20090720', 'Tax4', 4, 1, 1, 110, 10, 11
UNION SELECT 5, '20090721', 'Tax5', 5, 1, 1, 110, 10, 11

SELECT
	[Date]
	, [Tax Name]
	, [User Id]
	, [Client ID]
	, [Project ID]
	, [Total Amount]
	, [Tax Percent]
	, [Tax Amount]
FROM
	(
	SELECT
		[tax_id]
		, 1 AS [order]
		, CONVERT(CHAR(11), [date], 106) AS [Date]
		, [tax_name] AS [Tax Name]
		, CAST([user_Id] AS VARCHAR(50)) AS [User ID]
		, CAST([client_ID] AS VARCHAR(50)) AS [Client Id]
		, CAST([project_id] AS VARCHAR(50)) AS [Project Id]
		, [total_amount] AS [Total Amount]
		, CAST([tax_percent] AS VARCHAR(10)) AS [Tax Percent]
		, [tax_amount] AS [Tax Amount]
	FROM
		@foo
	UNION ALL SELECT
		[tax_id]
		, 2
		, ''
		, ''
		, ''
		, ''
		, 'Total:'
		, [total_amount]
		, 'Total:'
		, [tax_amount]
	FROM @foo
	)
	d
ORDER BY
	[tax_Id]
	, [order]

/*
Expected Output	


07/17/2009	Tax1	1	1	1	100	10	10	
Total:	100	Total:	10	
07/18/2009	Tax2	2	1	1	110	10	11	
Total:	110	Total:	11	
07/19/2009	Tax3	3	1	1	110	10	11	
Total:	110	Total:	11	
07/20/2009	Tax4	4	1	1	110	10	11	
Total:	110	Total:	11	
07/21/2009	Tax5	5	1	1	110	10	11	
Total:	110	Total:	11	
Grand Total:	540	Grand Total:	54	
*/

Use the database for what it is good at -- sorting and joining. Don't use it for formatting.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources