[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to make a temp table from an existing query

Posted on 2008-10-06
17
Medium Priority
?
298 Views
Last Modified: 2012-05-05
I' ve got the following query;

declare @Count1 as int
declare @Count2 as int
 set @Count1 = 1
 While @Count1 < 1000
begin
      set @Count2 = 1
      while @Count2 <= @Count1
      begin
            insert into QUAN values(@Count1)
            set @Count2 = @Count2 + 1
      end
      set @Count1 = @Count1 + 1
end

This wil fill my tabel automatically, dows somebody now how to make a TEMP table from this one. I don't like to have a table with more then 400.000 lines in it. (more then 400.000 lines when I will set '' While @Count1 < 1000''
0
Comment
Question by:AGION
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 5
17 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22650833
at the end of your loop, do this:


select * into #Temp from QUAN
0
 

Author Comment

by:AGION
ID: 22650923
The table QUAN is a table that I filled automatically with this query.
So in the new query, the table QUAN should not exist.
I want to create a temp table..without using the QUAN table. The query which would create the temp table should make the table QUAN unusable
 
 
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22650963
So are you just trying to populate a table with 400K numbers?

If so, use this.
;with
       cte0 as (select 1 as c union all select 1), -- 2
       cte1 as (select 1 as c from cte0 a, cte0 b), -- 4
       cte2 as (select 1 as c from cte1 a, cte1 b), -- 16
       cte3 as (select 1 as c from cte2 a, cte2 b), -- 256
       cte4 as (select 1 as c from cte3 a, cte3 b), -- 65,536
       cte5 as (select 1 as c from cte4 a, cte4 b), -- 4,294,967,296 --four BILLION, not million
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
     where n <= 400000

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22650970
to create a temp table from it...
;with
       cte0 as (select 1 as c union all select 1), -- 2
       cte1 as (select 1 as c from cte0 a, cte0 b), -- 4
       cte2 as (select 1 as c from cte1 a, cte1 b), -- 16
       cte3 as (select 1 as c from cte2 a, cte2 b), -- 256
       cte4 as (select 1 as c from cte3 a, cte3 b), -- 65,536
       cte5 as (select 1 as c from cte4 a, cte4 b), -- 4,294,967,296 --four BILLION, not million
       nums as (select row_number() over (order by c) as n from cte5)
       select n into #Numbers from nums 
     where n <= 400000

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22650975


I am not 100% I understand you..maybe something like this?

if object_id('tempdb..#counter') is not null
drop table #counter

create table #counter(intfield int)

declare @Count1 as int
declare @Count2 as int
 set @Count1 = 1
 While @Count1 < 1000
begin
      set @Count2 = 1
      while @Count2 <= @Count1
      begin
            insert into #counter values(@Count1)
            set @Count2 = @Count2 + 1
      end
      set @Count1 = @Count1 + 1
end
0
 

Author Comment

by:AGION
ID: 22651100
The original query I posted fills my table with the following data:
1
2
2
3
3
3
4
4
4
4
and so on.
I joined this table to the quantity in an order, So the Itemcode will be repeated as many times as the quantity. Now I can print labels from it.
The tabel i named QUAN has more than 400000 lines (it goes till 999*999)
Now I want to usea temp table instead of using the filled table QUAN.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22651260
Here you go...


;with
       cte0 as (select 1 as c union all select 1), -- 2
       cte1 as (select 1 as c from cte0 a, cte0 b), -- 4
       cte2 as (select 1 as c from cte1 a, cte1 b), -- 16
       cte3 as (select 1 as c from cte2 a, cte2 b), -- 256
       cte4 as (select 1 as c from cte3 a, cte3 b), -- 65,536
       cte5 as (select 1 as c from cte4 a, cte4 b), -- 4,294,967,296 --four BILLION, not million
       nums as (select row_number() over (order by c) as n from cte5)
 
     select n into #nums from nums 
     where n <= 999
;with NumsExpanded as
(select n1.*,row_number() over (partition by n1.n order by n1.n) rn
from #nums n1
cross join #nums n2)
select * from NumsExpanded
where rn<=n
go
drop table #nums

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22651286
actually, try this.  It gives you a #NumsExpanded temp table.  In there you will find

one 1
two 2
three 3
four -4
...
nine hundren ninety nine - 999
;with
       cte0 as (select 1 as c union all select 1), -- 2
       cte1 as (select 1 as c from cte0 a, cte0 b), -- 4
       cte2 as (select 1 as c from cte1 a, cte1 b), -- 16
       cte3 as (select 1 as c from cte2 a, cte2 b), -- 256
       cte4 as (select 1 as c from cte3 a, cte3 b), -- 65,536
       cte5 as (select 1 as c from cte4 a, cte4 b), -- 4,294,967,296 --four BILLION, not million
       nums as (select row_number() over (order by c) as n from cte5)
 
     select n into #nums from nums 
     where n <= 999
;with NumsExpanded as
(select n1.*,row_number() over (partition by n1.n order by n1.n) rn
from #nums n1
cross join #nums n2)
select * into #NumsExpanded from NumsExpanded
where rn<=n
select * from #NumsExpanded
go
drop table #NumsExpanded
drop table #nums

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22651354
The benefit of ^^ is that there is no looping so it should be very fast.  <4 seconds on my box.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22651407
>>The benefit of ^^ is that there is no looping so it should be very fast.
There definetly is looping going on...its just done w/o conventional iterative constructs.  :)
0
 

Author Comment

by:AGION
ID: 22651955
Okay, I don't think I explained myself very well....I will start from the beginnig;
I have a query, it gives me the following data
Linenum   Itemcode   Quantity
01                  A1000        2
02                  A2000        3
If I use the Table QUAN (filled with data generated from the query) and i will Join it with my table OINV it will repeat the Itemcode as many times as the quantity that is given in that line. Look to the attached image.
Now I don't want to use the QUAN table but I a temp table.
I think it should be a view ore something in the database....but the query's above don't help in this case
 
 
 

QUAN.jpg
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22651987
did you happen to look at the statement I gave you?

Either way, I am pretty sure the solutions Brandon and I provided will suffice either way....
0
 

Author Comment

by:AGION
ID: 22652066
if this is my select statement;
 SELECT     dbo.INV1.LineNum, dbo.INV1.ItemCode, dbo.INV1.Quantity
FROM INV1
How can I use your statement in it?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22652124

if object_id('tempdb..#counter') is not null
drop table #counter

create table #counter(intfield int)

declare @Count1 as int
declare @Count2 as int
 set @Count1 = 1
 While @Count1 < 1000
begin
      set @Count2 = 1
      while @Count2 <= @Count1
      begin
            insert into #counter values(@Count1)
            set @Count2 = @Count2 + 1
      end
      set @Count1 = @Count1 + 1
end


 SELECT     dbo.INV1.LineNum, dbo.INV1.ItemCode, dbo.INV1.Quantity

FROM INV1 i join #counter c on i.quantity <= c.intfield

0
 

Author Comment

by:AGION
ID: 22652208
If I use this one SQL 2005 says:
 
DROP TABLE SQL consrtuct or statement is not supported!
It use it i a view, else I cannot make a report on the data.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22652219
I have no idea why it would say that unless you pasted this into the view designer...paste this in a new query window

create table #counter(intfield int)

declare @Count1 as int
declare @Count2 as int
 set @Count1 = 1
 While @Count1 < 1000
begin
      set @Count2 = 1
      while @Count2 <= @Count1
      begin
            insert into #counter values(@Count1)
            set @Count2 = @Count2 + 1
      end
      set @Count1 = @Count1 + 1
end


 SELECT     dbo.INV1.LineNum, dbo.INV1.ItemCode, dbo.INV1.Quantity

FROM INV1 i join #counter c on i.quantity <= c.intfield
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1050 total points
ID: 22652246
You would be best to put it in a stored procedure and use the SP as your data source for your query.

But I challenge you this: "Now I don't want to use the QUAN table but I a temp table."

Why would you want to eliminate the static table of 490K+ records in favor of generating one on the fly.  I can assure you that any space savings you hope to achieve will be nothing compared to the cpu cycle savings by having the table there all the time.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question