funky script - 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

SQL Server 2000 Forums

SQL Server Development (2000)

funky script

Author

Topic

shmecher
Starting Member

10 Posts

Posted - 2005-01-14 : 10:57:52

Hi. I need to achieve the following and I just can't do it.
I have 2 tables: t1 and t2 with the following data:

t1
the_id the_value
------ ---------
860 35
860 25
860 50
860 50
861 10

t2
the_id the_value_total
------ ---------
860 80
861 5

I have to populate a table (#temp - same schema as t1) that would contain all the rows in t1 (matching the_id in t2) while the sum of the_value-s in t1 < the_value_total in t2. So, basically, for this data, my #temp table would contain:
the_id the_value
------ ---------
860 35
860 25

I tried the following script:

select the_id, the_value
into #t
from t1, t2
where t1.the_id = t2.the_id
and (select sum(the_value) from #t, t2 where #t.the_id = t2.the_id) < t2.the_value

but I get the following error:
"The SELECT INTO statement cannot have same source and destination tables."
I would appreciate any kind of help on how to approach this. Btw, cursors is something that I don't want to use.
Thanks
TIBM

shmecher
Starting Member

10 Posts

Posted - 2005-01-14 : 11:34:32

I acutally used
insert into ...
select ...
but I get no data. I understand why; the data is inserted in the table after the statement ends.
I don't think I can do this without cursors.
Thanks anyway.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-14 : 22:03:06

You can do this without cursors
IF there is a specific order to the records. if you can order each id by its value then you could do it.

a basic example:


declare @t1 table (entryId int identity(1,1), id int, val int)
Insert Into @t1
Select 860, 35
Union Select 860, 25
Union Select 860, 50
Union Select 860, 50
Union Select 861, 10

declare @t2 table (id int, val_ttl int)
Insert Into @t2
Select 860, 80
Union Select 861, 5


Select 
	A.entryId, 
	A.id, 
	A.val, 
	val_sum = (select sum(val) From @t1 where id = A.id and entryId <= A.entryId),
	B.val_ttl
From @t1 A
Inner Join @t2 B
On A.id = b.id
--Where val_ttl > (select sum(val) From @t1 where id = A.id and entryId <= A.entryId)

Corey

shmecher
Starting Member

10 Posts

Posted - 2005-01-17 : 09:45:38

That's smart and easy. Never thought about it... Thanks Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources