Create Temporary Tables Dynamically in cursor loop

Question

Post reply

Create Temporary Tables Dynamically in cursor loop

elmerbotha

Old Hand

Points: 331
More actions
June 19, 2008 at 3:32 am

#103510

I have a cursor and would like to create a temporary table for each record in that cursor. The name of each temporary table must be obtained partly from a value I get within the cursor data.
Something like
Open cursor;
Fetch Next from cursor into @Variable_Temp_Table_Name;
While @@Fetch_Status = 0
Begin
Create table # (pk int)
Fetch Next from cursor into @Variable_Temp_Table_Name;
End
Newbie says thanks.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

Good question! I do not know how beause of scoping problems. If you execute:

exec ('create table #what(id int)')

select * from #what

/* -------- Sample Output: --------

Msg 208, Level 16, State 0, Line 3

Invalid object name '#what'.

*/

The "#what" table never gets created within the same scope as the SELECT statement. You can change "#what" to "##what" but temp tables that start with ## are global temp tables rather than local temp tables. Use of global temp tables can have dangerous side affects.

GSquared SSC Guru Points: 260824 More actions · Answer 2

I don't think you can do what you're trying to do, and, even if you can, you probably shouldn't.

A cursor that creates an arbitrary number of temp tables with 1 row each? (If I'm understanding you correctly.) That sounds like the kind of code to use if you want to be rear-ended by a glacier.

If you really, really must do so, what you might want to do is create one temp table, insert XML data into it for each "sub table", and then use that. Of course, that's asking for an opportunity to be rear-ended by continental drift, but it would do something that might fit what you're asking for.

Alternately, post something about the end result you're trying to achieve, the code you have for it so far, and we might be able to help you arrive at a better solution.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

elmerbotha (6/19/2008)
I have a cursor and would like to create a temporary table for each record in that cursor. The name of each temporary table must be obtained partly from a value I get within the cursor data.

[font="Arial Black"]WHY???[/font] :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Ouch! :w00t:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

Honest to goodness... folks keep posting what they think is a solution to a problem instead of posting the actual problem. This particular one is RBAR on sterioids and is akin to flushing yourself down the toilet to try to get to the chow hall without going outside... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 6

Jeff Moden (6/19/2008)
Honest to goodness... folks keep posting what they think is a solution to a problem instead of posting the actual problem. This particular one is RBAR on sterioids and is akin to flushing yourself down the toilet to try to get to the chow hall without going outside... 😉

I tried to word my reply a bit more diplomatically, but it said essentially the same thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 7

Heh... I'm just not good at diplomacy when I see people trying to commit "Death by SQL". It's just absolutely impossible to pick up a turd by the clean end. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

elmerbotha Old Hand Points: 331 More actions · Answer 8

I truly feel honored to be graced by your absolutely meaningful and relevant answers. Truly, I am astounded by your infinite wisdom. Without risking being ridiculed once again by a bunch of super famous forum participants, I am going to approach this differently this time. Hopefully someone would be willing to stoop down to my level and give me some insight into how I could possibly approach this problem. Again, I am not proficient in SQL, so please bear with me. If I am at the wrong place, just say so. There are other forums out there.

I have a resultset with a bunch of lab results for certain precious metals which looks something like this:

Metal Result Unit of Measurement

Pt 1.26754 %

Pt 1 ppm

Au 4 ppm

Rd 8 %

etc, etc

What I have to do with the above is to per metal:

- Find the min value

- Find the mx value

-

Find the standard deviation

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

elmerbotha, can you please provide a larger sample size, say 5 or 10 rows for two metals? Presumably in your actual data you will have several rows like this:

Metal Result Unit of Measurement

Pt 1.26754 %

Pt 1.26324 %

Pt 1.26132 %

Pt 1.26971 %

Pt 1 ppm

Pt 2 ppm

Pt 1 ppm

and you want statistics by metal by UOM?

If you're unsure, have a quick read of the link at the bottom of Jeff Moden's post, it will help you to provide the information we need to solve your problem.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 10

elmerbotha (6/20/2008)
I truly feel honored to be graced by your absolutely meaningful and relevant answers. Truly, I am astounded by your infinite wisdom. Without risking being ridiculed once again by a bunch of super famous forum participants, I am going to approach this differently this time. Hopefully someone would be willing to stoop down to my level and give me some insight into how I could possibly approach this problem. Again, I am not proficient in SQL, so please bear with me. If I am at the wrong place, just say so. There are other forums out there.
I have a resultset with a bunch of lab results for certain precious metals which looks something like this:
Metal Result Unit of Measurement
Pt 1.26754 %
Pt 1 ppm
Au 4 ppm
Rd 8 %
etc, etc
What I have to do with the above is to per metal:
- Find the min value
- Find the mx value
-
Find the standard deviation

I can tell you that your talent for sarcasm isn't going to buy you much either. 😉

C'mon... if you're not a scientist, you at least work with them. You know bloody well that when you're new in a field, you don't go in telling folks how you want to do something. You present the real problem correctly and let them tell you how to do it. Your original request was like saying you wanted to add the water to the acid... of course you're going to be joked at a bit.

Now, if you have the time to take the chip off your shoulder and, maybe, humble down a bit to realize that you're the person asking for help, maybe we can get down to business... 😉

This is a simple problem and it certainly doesn't require the overhead or the slothfulness of a cursor and While loop...

Here's the data you provided along with the solution... it would be helpful if you provided it in this format in the future. It will also get you an answer a lot quicker no matter which forum you may frequent. A good way to create data in this format is discussed in the URL in my signature...

--===== This simulates your result set

CREATE TABLE #YourHead

(Metal VARCHAR(5),Result DECIMAL(10,9), UoM VARCHAR(5))

INSERT INTO #YourHead

(Metal,Result,UoM)

SELECT 'Pt','1.26754','%' UNION ALL

SELECT 'Pt','1.41421','%' UNION ALL

SELECT 'Pt','1.73205','ppm' UNION ALL

SELECT 'Pt','1' ,'ppm' UNION ALL

SELECT 'Au','4' ,'ppm' UNION ALL

SELECT 'Rd','8' ,'%'

--===== This produces the results you want without a cursor

-- and without a While loop. It also produces a couple

-- of other things just so you can see how it's done.

SELECT Metal,

MIN(Result) AS MinResult,

CAST(AVG(Result) AS DECIMAL(10,9)) AS AvgResult,

MAX(Result) AS MaxResult,

COUNT(*) AS SampleCount,

Uom AS [Unit Of Measure]

FROM #YourHead

GROUP BY Metal,UoM

--===== Don't need this for production in a stored proc but,

-- this allows for multiple test runs

DROP TABLE #YourHead

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 11

Oh yeah... almost forgot... if you'll leave the flippant attitude and sarcasm at home and post the code that produces your result set, we can also show you how to integrate the solution above with that which produces your result set. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Try this:

SELECT Metal, min(Result), max(Result), count(Result), stdev(Result)

FROM Metals

GROUP BY Metal

By the way, stdev() is the statistical standard deviation, if you want the standard deviation for a population , use stdevp() instead.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Sounds to me that even once you have your data organized, you have the probably non-trivial task of making sure you have conversion scenarios between unit notations. You're also going to need something to show you units that CAN be compared. For example 2ppm is usually substantially less than 1.25%, but try showing that right now without a unit conversion routine.

I also suspect you have quite a few more units than just those.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 14

rbarryyoung (6/20/2008)
Try this:
SELECT Metal, min(Result), max(Result), count(Result), stdev(Result)
FROM Metals
GROUP BY Metal
By the way, stdev() is the statistical standard deviation, if you want the standard deviation for a population , use stdevp() instead.

Barry... you have to include the "Unit of Measure" in the Group By... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)