Solved

How to make a temp table from an existing query

Posted on 2008-10-06
17
253 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 350 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 36
SQL Server agent & maintenance task issue ? 8 65
SQl server restarts itself 6 33
Sql Query join multiple table and distinct records 7 29
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now