?
Solved

How to make a temp table from an existing query

Posted on 2008-10-06
17
Medium Priority
?
292 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 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