We help IT Professionals succeed at work.

How to make a temp table from an existing query

AGION
AGION asked
on
328 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''
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
at the end of your loop, do this:


select * into #Temp from QUAN

Author

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

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

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:


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

Author

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

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

The benefit of ^^ is that there is no looping so it should be very fast.  <4 seconds on my box.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
>>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.  :)

Author

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
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
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....

Author

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?
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:

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

Author

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.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.