How to make a temp table from an existing query

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''
AGIONAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
chapmandewCommented:
at the end of your loop, do this:


select * into #Temp from QUAN
0
 
AGIONAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
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
 
chapmandewCommented:


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
 
AGIONAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
The benefit of ^^ is that there is no looping so it should be very fast.  <4 seconds on my box.
0
 
chapmandewCommented:
>>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
 
AGIONAuthor Commented:
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
 
chapmandewCommented:
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
 
AGIONAuthor Commented:
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
 
chapmandewCommented:

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
 
AGIONAuthor Commented:
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
 
chapmandewCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.